<?php

namespace App\Model;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;

class Statistics extends BaseModel
{
    protected $userRegisterTable = 'user_register_log';
    protected $connection = 'api';
    protected $roomTable = 'room';
    protected $unionTable = 'union';
    protected $unionUserTable = 'union_user';
    protected $giftTableV2 = 'gift_library';
    protected $financeWalletTable = 'finance_wallet';
    protected $financeGameRankTable = 'finance_game_rank';
    protected $gameRankRewardLogTable = 'game_rank_reward_log';
    protected $room_pk_table = 'room_pk';
    protected $room_pk_user_log_table = 'room_pk_user_log';
    protected $userTable = 'user';
    protected $unionRoomTable = 'union_room';
    protected $roomOpenTimeTable = 'room_open_time';
    protected $unionRoomStreamTable = 'finance_union_room_stream';
    protected $unionRoomStreamTableDays = 'finance_union_room_stream_days';
    protected $unionRoomWeekrewardTable = 'union_room_weekreward';
    protected $giftTable = 'gift_library';
    protected $giftStreamTable = 'gift_stream';
    protected $giftLuckLogTable = 'gift_luck_log';
    protected $giftBagTable = 'gift_bag';
    protected $userMsgTable = 'user_msg';
    protected $lybTable = 'lyb_event';//留言板
    protected $colorGiftRateAwardStreamTable = 'color_gift_rate_award_stream';//彩蛋礼物中奖记录表
    protected $luckDrawTable = 'luck_draw';//许愿池表
    protected $luckDrawAwardLogTable = 'luck_draw_award_log';//许愿池中奖记录表
    protected $wishWheelDrawTable = 'wish_wheel_draw';//许愿转盘-抽奖记录
    protected $hopePoolsDrawTable = 'hope_pools_draw';//许愿转盘-抽奖记录
    protected $wishPoolsDrawTable = 'wish_pools_draw';//星辰变抽奖记录
    protected $wishDefenderDrawTable = 'wish_defender_draw';//擂主游戏抽奖记录

    protected $activityConn = 'activity';
    protected $jmDrawQrcodeTable = 'jm_draw_qrcode';
    protected $ssy2023EventTable = 'ssy2023_event';
    protected $ssy2023ShellTable = 'ssy2023_shell';
    protected $sse2023EventTable = 'sse2023_event';
    protected $sse2023ShellTable = 'sse2023_shell';
    protected $sdj_bsym_event_table = 'sdj_bsym_event';
    protected $sdj_bsym_reward_table = 'sdj_bsym_reward';
    protected $sdj_hyll_event_table = 'sdj_hyll_event';
    protected $sdj_hyll_darw_table = 'sdj_hyll_darw';
    protected $sdj_hyll_day_stat_table = 'sdj_hyll_day_stat';
    protected $sdj_hyll_stat_table = 'sdj_hyll_stat';
    protected $sdj_lyb_event_table = 'sdj_lyb_event';
    protected $lc_event_v3_table = 'grasslandsheep_event_v3';

    protected $statCon = 'statistic';
    protected $statDailyTable = 'stat_daily';
    protected $newDailyRoomTable = 'new_daily_room';
    protected $gameDayStatistics_table = 'game_day_statistics';
    protected $gameRoomDayStatistics_table = 'game_room_day_statistics';
    protected $dailyRoomDataTable = 'daily_room_data';//每日统计表

    //每日统计
    public function statDaily(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');

        $query = DB::connection($this->statCon)->table($this->statDailyTable)->where('type', 1);
        if (!empty($created_st)) {
            $query = $query->where('created_date', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $query = $query->where('created_date', '<=', $created_ed);
        }
        $result = $query->paginate($limit, ['*'], 'page', $page);
        return $result;
    }

    /**
     *  新版本数据每日流水统计(主播数据)
     */
    public function getDaiylRoom($page, $limit, $roomId, $startTime, $endTime, $unionId, $union_name, $room_name)
    {
        $query = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
            ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
            ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
            ->select('a.id', 'a.union_id as unionId', 'a.room_id as roomId', 'a.total as useDiamondAll', 'a.created_at as created_date', 'b.name as unionName', 'c.name as roomName', 'c.room_uid');
        if (!empty($startTime)) {
            $query = $query->where('a.created_at', '>=', date('Y-m-d', strtotime($startTime)));
        }
        if (!empty($endTime)) {
            $query = $query->where('a.created_at', '<=', date('Y-m-d', strtotime($endTime)));
        }
        if (!empty($roomId)) {
            $query = $query->where('a.room_Id', $roomId);
        }
        if (!empty($unionId)) {
            $query = $query->where('a.union_Id', $unionId);
        }
        if (!empty($union_name)) {
            $query = $query->where('b.name', 'like', "%{$union_name}%");
        }
        if (!empty($room_name)) {
            $query = $query->where('c.name', 'like', "%{$room_name}%");
        }

        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        //        if (!empty($result['data'])) {
//            foreach ($result['data'] as $k => $val) {
//                $result['data'][$k]['roomName'] = '';
//                $result['data'][$k]['unionName'] = '';
//                if (!empty($val['roomId'])) {
//                    $room = DB::connection($this->connection)->table($this->roomTable)->where('room_id', $val['roomId'])->first();
//                    $room = objToArray($room);
//                    if (!empty($room)) {
//                        $result['data'][$k]['roomName'] = $room['name'];
//                        $result['data'][$k]['room_uid'] = $room['room_uid'];
//                    }
//                }
//                if (!empty($val['unionId'])) {
//                    $union = DB::connection($this->connection)->table($this->unionTable)->where('id', $val['unionId'])->first();
//                    $union = objToArray($union);
//                    if (!empty($union)) {
//                        $result['data'][$k]['unionName'] = $union['name'];
//                    }
//                }
//
//            }
//        }
        return $result;
    }

    //    public function getDaiylRoom2($page,$limit,$roomId,$startTime,$endTime,$unionId,$union_name,$room_name)
//    {
//        $query = DB::connection($this->connection)->table($this->unionRoomStreamTable.' as a')
//            ->leftJoin($this->unionTable.' as b', "a.union_id", "=", "b.id")
//            ->leftJoin($this->roomTable.' as c', "a.room_id", "=", "c.room_id")
//            ->whereIn('a.data_type', [1, 2])
//            ->select('a.*',DB::raw('sum(data_value*data_num) as useDiamondAll'),'b.name as union_name','c.name as room_name')
////            ->selectRaw('a.*,SUM(a.data_value * a.data_num) as useDiamondAll')
//            ->groupBy('a.room_id');
//        if(!empty($startTime)) {
////            $query = $query->where('created_at', '>=', date('Y-m-d',strtotime($startTime)));
//            $query = $query->where('a.created_at', '>=', $startTime.' 00:00:00');
//        }
//        if(!empty($endTime)) {
////            $query = $query->where('created_at', '<=', date('Y-m-d',strtotime($endTime)));
//            $query = $query->where('a.created_at', '<=', $endTime.' 23:59:59');
//        }
//        if (!empty($unionId))
//        {
//            $query = $query->where('a.union_id', $unionId);
//        }
//        if (!empty($roomId))
//        {
//            $query = $query->where('a.room_id', $roomId);
//        }
//        if (!empty($union_name))
//        {
//            $query = $query->where('b.name', 'like', "%{$union_name}%");
//        }
//        if (!empty($room_name))
//        {
//            $query = $query->where('c.name',  'like', "%{$room_name}%");
//        }
//        $result = $query->paginate($limit, ['*'], 'page', $page);
//        $result = objToArray($result);
//        if (!empty($result['data']))
//        {
//            foreach ($result['data'] as $k=>$val)
//            {
//                $date = date('Y-m-d', strtotime($val['created_at']));
//                //查询房间流水数据统计用户总数
//                $userIds =DB::connection($this->connection)->table('finance_union_room_stream')
//                    ->where('room_id', $val['room_id'])
//                    ->where('created_at', '>=', $startTime.' 00:00:00')
//                    ->where('created_at', '<=', $endTime.' 23:59:59')
//                    ->select(['from_user_id'])
//                    ->distinct()
//                    ->get();
//                $userIds = self::objToArray($userIds);
//                $userIds = array_column($userIds, 'from_user_id');
//                $rechargeUserNum = count($userIds);
////                查询数据中用户来源
//                $registerFrom =DB::connection($this->connection)->table($this->userRegisterTable)->whereIn('user_id', $userIds)
//                    ->where('os_name', 'Android')
//                    ->select('user_id')
//                    ->distinct()
//                    ->get();
//                $userAndroid = self::objToArray($registerFrom);
//                $useDiamondNumAndroid = count($userAndroid);
////                $registerFrom = DB::connection($this->connection)->table($this->userRegisterTable)->where('user_id', $val['from_user_id'])->orderBy('time', 'ASC')->first();
////                $registerFrom = self::objToArray($registerFrom);
////                $allnum = $val['data_value'] * $val['data_num'];
//                $result['data'][$k]['roomName'] = '';
//                $result['data'][$k]['unionName'] = '';
//                $result['data'][$k]['useDiamondAll'] = $val['useDiamondAll'];
//                $result['data'][$k]['roomId'] = $val['room_id'];
//                $result['data'][$k]['unionId'] = $val['union_id'];
//                $result['data'][$k]['created_date'] = $val['created_at'];
//                $result['data'][$k]['useDiamondUserNum'] = $rechargeUserNum;
//                $result['data'][$k]['useDiamondNumIos'] = $rechargeUserNum-$useDiamondNumAndroid;
//                $result['data'][$k]['useDiamondNumAndroid'] = $useDiamondNumAndroid;
////                if ($registerFrom['os_name'] == 'iPhone') {
////                    $result['data'][$k]['useDiamondTotalIos'] = $allnum;
////                }else{
////                    $result['data'][$k]['useDiamondTotalAndroid'] = $allnum;
////                }
//                if(!empty($val['room_id'])) {
//                    $room = DB::connection($this->connection)->table($this->roomTable)->where('room_id', $val['room_id'])->first();
//                    $room = objToArray($room);
//                    if(!empty($room)) {
//                        $result['data'][$k]['roomName'] = $room['name'];
//                        $result['data'][$k]['room_uid'] = $room['room_uid'];
//                    }
//                }
//                if(!empty($val['union_id'])) {
//                    $union = DB::connection($this->connection)->table($this->unionTable)->where('id', $val['union_id'])->first();
//                    $union = objToArray($union);
//                    if(!empty($union)) {
//                        $result['data'][$k]['unionName'] = $union['name'];
//                    }
//                }
//
//            }
//        }
//        return $result;
//    }

    public function getDaiylRoom2($page, $limit, $roomId, $startTime, $endTime, $unionId, $union_name, $room_name)
    {
        $query = DB::connection($this->connection)->table($this->unionRoomStreamTable . ' as a')
            ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
            ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
            ->whereIn('a.data_type', [1, 2])
            ->select('a.*', DB::raw('sum(data_value*data_num) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
            //            ->selectRaw('a.*,SUM(a.data_value * a.data_num) as useDiamondAll')
            ->groupBy('a.room_id');
        if (!empty($startTime)) {
            //            $query = $query->where('created_at', '>=', date('Y-m-d',strtotime($startTime)));
            $query = $query->where('a.created_at', '>=', $startTime . ' 00:00:00');
        }
        if (!empty($endTime)) {
            //            $query = $query->where('created_at', '<=', date('Y-m-d',strtotime($endTime)));
            $query = $query->where('a.created_at', '<=', $endTime . ' 23:59:59');
        }
        if (!empty($unionId)) {
            $query = $query->where('a.union_id', $unionId);
        }
        if (!empty($roomId)) {
            $query = $query->where('a.room_id', $roomId);
        }
        if (!empty($union_name)) {
            $query = $query->where('b.name', 'like', "%{$union_name}%");
        }
        if (!empty($room_name)) {
            $query = $query->where('c.name', 'like', "%{$room_name}%");
        }
        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        // dd($result);
        $roomIds = array_column($result['data'], 'room_id');

        $list = DB::connection($this->connection)->table($this->unionRoomStreamTable)
            ->whereIn('room_id', $roomIds)
            ->where('created_at', '>=', $startTime . ' 00:00:00')
            ->where('created_at', '<=', $endTime . ' 23:59:59')
            ->groupBy('room_id')
            ->select('room_id', DB::raw('count(distinct from_user_id) as useDiamondUserNum'))->get();
        $list = objToArray($list);
        // dd($list);

        if (!empty($result['data'])) {
            foreach ($result['data'] as $k => $val) {
                $row = array_filter($list, function ($item) use ($val) {
                    return $item['room_id'] == $val['room_id'];
                });
                $row = reset($row);
                $result['data'][$k]['useDiamondUserNum'] = $row ? $row['useDiamondUserNum'] : 0;

                $result['data'][$k]['roomName'] = '';
                $result['data'][$k]['unionName'] = '';
                $result['data'][$k]['useDiamondAll'] = $val['useDiamondAll'];
                $result['data'][$k]['roomId'] = $val['room_id'];
                $result['data'][$k]['unionId'] = $val['union_id'];
                $result['data'][$k]['created_date'] = $val['created_at'];
                $result['data'][$k]['useDiamondNumIos'] = 0;
                $result['data'][$k]['useDiamondNumAndroid'] = 0;

                $result['data'][$k]['roomName'] = $val['room_name'];
                $result['data'][$k]['roomUid'] = $val['room_uid'];
                $result['data'][$k]['unionName'] = $val['union_name'];
            }
        }
        return $result;
    }

    public function getDaiylRoom2New($page, $limit, $roomId, $startTime, $endTime, $unionId, $union_name, $room_name)
    {
        $query = null;
        $jiaoji=false;
        if (date("Y-m-d", strtotime($startTime)) != date("Y-m-d", strtotime($endTime))) { //大于 1 天
            if (strtotime($endTime) >= strtotime(date("Y-m-d"))) { //包含今天
                $Today = date("Y-m-d");
                $jiaoji=true;
                $endTime = date("Y-m-d", strtotime("yesterday"));
                // dd($endTime,$startTime);
                $query = DB::connection($this->connection)
                    ->query()
                    ->fromSub(function ($query) use ($Today, $startTime, $endTime, $unionId, $roomId, $union_name, $room_name) {
                        $query1 = DB::connection($this->connection)->table($this->unionRoomStreamTable . ' as a')
                            ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                            ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                            ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(data_value*data_num) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                            ->whereRaw('zb_a.data_type in (1,2)')
                            ->whereRaw('zb_a.created_at <= ? AND zb_a.created_at >= ?', [date("Y-m-d 23:59:59", strtotime($Today)), date("Y-m-d 00:00:00", strtotime($Today))])
                            ->groupBy('a.union_id', 'a.room_id')
                            ->groupBy(DB::raw('DATE_FORMAT(zb_a.created_at, "%Y-%m-%d")'));

                        if (!empty($unionId)) {
                            $query1 = $query1->whereRaw('zb_a.union_id = ?', [$unionId]);
                        }
                        if (!empty($roomId)) {
                            $query1 = $query1->whereRaw('zb_a.room_id = ?', [$roomId]);
                        }
                        if (!empty($union_name)) {
                            $query1 = $query1->whereRaw('zb_b.name like ?', ["%{$union_name}%"]);
                        }
                        if (!empty($room_name)) {
                            $query1 = $query1->whereRaw('zb_c.name like ?', ["%{$room_name}%"]);
                        }

                        $query2 = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                            ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                            ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                            ->whereRaw('zb_a.created_at <= ? AND zb_a.created_at >= ?', [date("Y-m-d", strtotime($endTime)), date("Y-m-d", strtotime($startTime))])
                            ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(total) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                            ->groupBy('a.union_id', 'a.room_id')
                            ->groupBy('a.created_at');
                        if (!empty($unionId)) {
                            $query2 = $query2->whereRaw('zb_a.union_id = ?', [$unionId]);
                        }
                        if (!empty($roomId)) {
                            $query2 = $query2->whereRaw('zb_a.room_id = ?', [$roomId]);
                        }

                        if (!empty($union_name)) {
                            $query2 = $query2->whereRaw('zb_b.name like ?', ["%{$union_name}%"]);
                        }
                        if (!empty($room_name)) {
                            $query2 = $query2->whereRaw('zb_c.name like ?', ["%{$room_name}%"]);
                        }

                        $query->fromSub($query1, 'u1')
                            ->unionAll($query2);

                        // dd($query->toSql(),[date("Y-m-d 23:59:59", strtotime($Today)), date("Y-m-d 00:00:00", strtotime($Today))],[date("Y-m-d", strtotime($endTime)), date("Y-m-d", strtotime($startTime))]);
                    }, 'a');
            } else { //不包含今天
                $query = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                    ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                    ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                    ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(total) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                    ->where('a.created_at', '<=', date("Y-m-d", strtotime($endTime)))
                    ->where('a.created_at', '>=', date("Y-m-d", strtotime($startTime)))
                    ->groupBy('a.union_id', 'a.room_id')
                    ->groupBy('a.created_at');
        if (!empty($unionId)) {
                    $query = $query->where('a.union_id', $unionId);
                }
                if (!empty($roomId)) {
                    $query = $query->where('a.room_id', $roomId);
                }
                if (!empty($union_name)) {
                    $query = $query->where('b.name', 'like', "%{$union_name}%");
                }
                if (!empty($room_name)) {
                    $query = $query->where('c.name', 'like', "%{$room_name}%");
                }
            }
        } else { //等于 1 天
            $day = $startTime;
            if ($day == date("Y-m-d")) { //今天
                $query = DB::connection($this->connection)->table($this->unionRoomStreamTable . ' as a')
                    ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                    ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                    ->select('a.union_id', 'a.room_id','a.created_at', DB::raw('sum(data_value*data_num) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                    ->whereIn('a.data_type', [1, 2])
                    ->where('a.created_at', '<=', date("Y-m-d 23:59:59", strtotime($day)))
                    ->where('a.created_at', '>=', date("Y-m-d 00:00:00", strtotime($day)))
                    ->groupBy('a.union_id', 'a.room_id')
                    ->groupBy(DB::raw('DATE_FORMAT(zb_a.created_at, "%Y-%m-%d")'));
            } else { //非今天
                $query = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                    ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                    ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                    ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(total) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                    ->where('a.created_at', '=', date("Y-m-d", strtotime($day)))
                    ->groupBy('a.union_id', 'a.room_id')
                    ->groupBy('a.created_at');
    }
            if (!empty($unionId)) {
                $query = $query->where('a.union_id', $unionId);
            }
            if (!empty($roomId)) {
                $query = $query->where('a.room_id', $roomId);
            }
            if (!empty($union_name)) {
                $query = $query->where('b.name', 'like', "%{$union_name}%");
            }
            if (!empty($room_name)) {
                $query = $query->where('c.name', 'like', "%{$room_name}%");
            }
        }

        $query = $query->orderBy('a.created_at', 'desc');


        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        if (!empty($result['data'])) {
            foreach ($result['data'] as $k => $val) {
                $result['data'][$k]['id'] = $limit * ($page - 1) + ($k + 1);
                $result['data'][$k]['useDiamondUserNum'] = '';
                $result['data'][$k]['roomName'] = '';
                $result['data'][$k]['unionName'] = '';
                $result['data'][$k]['roomId'] = $val['room_id'];
                $result['data'][$k]['unionId'] = $val['union_id'];
                $result['data'][$k]['created_date'] = date('Y-m-d', strtotime($val['created_at']));
                $result['data'][$k]['useDiamondNumIos'] = 0;
                $result['data'][$k]['useDiamondNumAndroid'] = 0;

                $result['data'][$k]['roomName'] = $val['room_name'];
                $result['data'][$k]['roomUid'] = $val['room_uid'];
                $result['data'][$k]['unionName'] = $val['union_name'];
            }
        }
        return $result;
    }
    public function getDaiylRoom2NewV2($page, $limit, $roomId, $startTime, $endTime, $unionId, $union_name, $room_name)
    {
        $query = null;
        $jiaoji=false;
        $notOne=false;
        $notOneUseStartTime=date("Y-m-d", strtotime($startTime));
        $notOneUseEndTime=date("Y-m-d", strtotime($endTime));
        if (date("Y-m-d", strtotime($startTime)) != date("Y-m-d", strtotime($endTime))) { //大于 1 天
            if (strtotime($endTime) >= strtotime(date("Y-m-d"))) { //包含今天
                $Today = date("Y-m-d");
                $jiaoji=true;
                $endTime = date("Y-m-d", strtotime("yesterday"));
                // dd($endTime,$startTime);
                $query = DB::connection($this->connection)
                    ->query()
                    ->fromSub(function ($query) use ($Today, $startTime, $endTime, $unionId, $roomId, $union_name, $room_name) {
                        $query1 = DB::connection($this->connection)->table($this->unionRoomStreamTable . ' as a')
                            ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                            ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                            ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(data_value*data_num) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                            ->whereRaw('zb_a.data_type in (1,2)')
                            ->whereRaw('zb_a.created_at <= ? AND zb_a.created_at >= ?', [date("Y-m-d 23:59:59", strtotime($Today)), date("Y-m-d 00:00:00", strtotime($Today))])
                            ->groupBy('a.union_id', 'a.room_id')
                            ->groupBy(DB::raw('DATE_FORMAT(zb_a.created_at, "%Y-%m-%d")'));

                        if (!empty($unionId)) {
                            $query1 = $query1->whereRaw('zb_a.union_id = ?', [$unionId]);
                        }
                        if (!empty($roomId)) {
                            $query1 = $query1->whereRaw('zb_a.room_id = ?', [$roomId]);
                        }
                        if (!empty($union_name)) {
                            $query1 = $query1->whereRaw('zb_b.name like ?', ["%{$union_name}%"]);
                        }
                        if (!empty($room_name)) {
                            $query1 = $query1->whereRaw('zb_c.name like ?', ["%{$room_name}%"]);
                        }

                        $query2 = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                            ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                            ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                            ->whereRaw('zb_a.created_at <= ? AND zb_a.created_at >= ?', [date("Y-m-d", strtotime($endTime)), date("Y-m-d", strtotime($startTime))])
                            ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(total) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                            ->groupBy('a.union_id', 'a.room_id')
                            ->groupBy('a.created_at');
                        if (!empty($unionId)) {
                            $query2 = $query2->whereRaw('zb_a.union_id = ?', [$unionId]);
                        }
                        if (!empty($roomId)) {
                            $query2 = $query2->whereRaw('zb_a.room_id = ?', [$roomId]);
                        }

                        if (!empty($union_name)) {
                            $query2 = $query2->whereRaw('zb_b.name like ?', ["%{$union_name}%"]);
                        }
                        if (!empty($room_name)) {
                            $query2 = $query2->whereRaw('zb_c.name like ?', ["%{$room_name}%"]);
                        }

                        $query->fromSub($query1, 'u1')
                            ->unionAll($query2);

                        // dd($query->toSql(),[date("Y-m-d 23:59:59", strtotime($Today)), date("Y-m-d 00:00:00", strtotime($Today))],[date("Y-m-d", strtotime($endTime)), date("Y-m-d", strtotime($startTime))]);
                    }, 'a')
                    ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(useDiamondAll) as useDiamondAll'), 'union_name', 'room_name', 'room_uid')
                    ->groupBy('a.union_id', 'a.room_id');
            } else { //不包含今天
                $query = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                    ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                    ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                    ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(total) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                    ->where('a.created_at', '<=', date("Y-m-d", strtotime($endTime)))
                    ->where('a.created_at', '>=', date("Y-m-d", strtotime($startTime)))
                    ->groupBy('a.union_id', 'a.room_id');
//                    ->groupBy('a.created_at');
                if (!empty($unionId)) {
                    $query = $query->where('a.union_id', $unionId);
                }
                if (!empty($roomId)) {
                    $query = $query->where('a.room_id', $roomId);
                }
                if (!empty($union_name)) {
                    $query = $query->where('b.name', 'like', "%{$union_name}%");
                }
                if (!empty($room_name)) {
                    $query = $query->where('c.name', 'like', "%{$room_name}%");
                }
            }
            $notOne=true;
        } else { //等于 1 天
            $day = $startTime;
            if ($day == date("Y-m-d")) { //今天
                $query = DB::connection($this->connection)->table($this->unionRoomStreamTable . ' as a')
                    ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                    ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                    ->select('a.union_id', 'a.room_id','a.created_at', DB::raw('sum(data_value*data_num) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                    ->whereIn('a.data_type', [1, 2])
                    ->where('a.created_at', '<=', date("Y-m-d 23:59:59", strtotime($day)))
                    ->where('a.created_at', '>=', date("Y-m-d 00:00:00", strtotime($day)))
                    ->groupBy('a.union_id', 'a.room_id')
                    ->groupBy(DB::raw('DATE_FORMAT(zb_a.created_at, "%Y-%m-%d")'));
            } else { //非今天
                $query = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                    ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                    ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                    ->select('a.union_id', 'a.room_id', 'a.created_at', DB::raw('sum(total) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
                    ->where('a.created_at', '=', date("Y-m-d", strtotime($day)))
                    ->groupBy('a.union_id', 'a.room_id')
                    ->groupBy('a.created_at');
            }
            if (!empty($unionId)) {
                $query = $query->where('a.union_id', $unionId);
            }
            if (!empty($roomId)) {
                $query = $query->where('a.room_id', $roomId);
            }
            if (!empty($union_name)) {
                $query = $query->where('b.name', 'like', "%{$union_name}%");
            }
            if (!empty($room_name)) {
                $query = $query->where('c.name', 'like', "%{$room_name}%");
            }
        }

        $query = $query->orderBy('a.created_at', 'desc');


        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        if (!empty($result['data'])) {
            foreach ($result['data'] as $k => $val) {
                $result['data'][$k]['id'] = $limit * ($page - 1) + ($k + 1);
                $result['data'][$k]['useDiamondUserNum'] = '';
                $result['data'][$k]['roomName'] = '';
                $result['data'][$k]['unionName'] = '';
                $result['data'][$k]['roomId'] = $val['room_id'];
                $result['data'][$k]['unionId'] = $val['union_id'];
                $result['data'][$k]['created_date'] = $notOne === false ? date('Y-m-d', strtotime($val['created_at'])) : $notOneUseStartTime . ' - ' . $notOneUseEndTime;
                $result['data'][$k]['useDiamondNumIos'] = 0;
                $result['data'][$k]['useDiamondNumAndroid'] = 0;

                $result['data'][$k]['roomName'] = $val['room_name'];
                $result['data'][$k]['roomUid'] = $val['room_uid'];
                $result['data'][$k]['unionName'] = $val['union_name'];
            }
        }
        return $result;
    }


    public function getDaiylRoom2_Today()
    {
        $query = DB::connection($this->connection)->table($this->unionRoomStreamTable . ' as a')
            ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
            ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
            ->whereIn('a.data_type', [1, 2])
            ->select('a.*', DB::raw('sum(data_value*data_num) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid')
            //            ->selectRaw('a.*,SUM(a.data_value * a.data_num) as useDiamondAll')
            ->groupBy('a.room_id');
        if (!empty($startTime)) {
            //            $query = $query->where('created_at', '>=', date('Y-m-d',strtotime($startTime)));
            $query = $query->where('a.created_at', '>=', $startTime . ' 00:00:00');
        }
        if (!empty($endTime)) {
            //            $query = $query->where('created_at', '<=', date('Y-m-d',strtotime($endTime)));
            $query = $query->where('a.created_at', '<=', $endTime . ' 23:59:59');
        }
        if (!empty($unionId)) {
            $query = $query->where('a.union_id', $unionId);
        }
        if (!empty($roomId)) {
            $query = $query->where('a.room_id', $roomId);
        }
        if (!empty($union_name)) {
            $query = $query->where('b.name', 'like', "%{$union_name}%");
        }
        if (!empty($room_name)) {
            $query = $query->where('c.name', 'like', "%{$room_name}%");
        }
        $result = $query->get();
        $result = objToArray($result);
        return $result;
    }


    public function getDaiylRoom2Days($page, $limit, $roomId, $startTime, $endTime, $unionId, $union_name, $room_name)
    {

        //非，今天且单天数据
        if (!(date("Y-m-d", strtotime($startTime)) == date("Y-m-d") && date("Y-m-d", strtotime($endTime)) == date("Y-m-d"))) {
            $query = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                ->leftJoin($this->unionTable . ' as b', "a.union_id", "=", "b.id")
                ->leftJoin($this->roomTable . ' as c', "a.room_id", "=", "c.room_id")
                //                ->whereIn('a.data_type', [1, 2])
                ->select('a.*', DB::raw('sum(total) as useDiamondAll'), 'b.name as union_name', 'c.name as room_name', 'c.room_uid');
            //            ->selectRaw('a.*,SUM(a.data_value * a.data_num) as useDiamondAll')
//                ->groupBy('a.room_id');
            if (!empty($startTime)) {
                //            $query = $query->where('created_at', '>=', date('Y-m-d',strtotime($startTime)));
                $query = $query->where('a.created_at', '>=', date("Y-m-d", strtotime($startTime)));
            }
            if (!empty($endTime)) {
                //            $query = $query->where('created_at', '<=', date('Y-m-d',strtotime($endTime)));
                $query = $query->where('a.created_at', '<=', date("Y-m-d", strtotime($endTime)));
            }
            if (!empty($unionId)) {
                $query = $query->where('a.union_id', $unionId);
            }
            if (!empty($roomId)) {
                $query = $query->where('a.room_id', $roomId);
            }
            if (!empty($union_name)) {
                $query = $query->where('b.name', 'like', "%{$union_name}%");
            }
            if (!empty($room_name)) {
                $query = $query->where('c.name', 'like', "%{$room_name}%");
            }
            $result = $query->paginate($limit, ['*'], 'page', $page);
            $result = objToArray($result);
            //          dd($result);
//            $roomIds = array_column($result['data'], 'room_id');
//            $list = DB::connection($this->connection)->table($this->unionRoomStreamTable)
//                ->whereIn('room_id', $roomIds)
//                ->where('created_at', '>=', $startTime . ' 00:00:00')
//                ->where('created_at', '<=', $endTime . ' 23:59:59')
//                ->groupBy('room_id')
//                ->select('room_id', DB::raw('count(distinct from_user_id) as useDiamondUserNum'))->get();
//            $list = objToArray($list);
//            dd($list);
//            dd($result);
            $dayRoomBill = $result['data'];
            //            dd($dayRoomBill);
            if (date("Y-m-d", strtotime($endTime)) == date("Y-m-d")) {
                $todayList = $this->getDaiylRoom2_Today();
                if (!empty($todayList)) {
                    if (empty($dayRoomBill)) {
                        $dayRoomBill = $todayList;
                    } else {
                        $mergedList = [];
                        $keyedTodayList = [];

                        // Create lookup array for today's list
                        foreach ($todayList as $today) {
                            $key = $today['room_id'] . '_' . $today['time'];
                            $keyedTodayList[$key] = $today;
                        }

                        // Process dayRoomBill and merge with today's data
                        foreach ($dayRoomBill as $day) {
                            $key = $day['room_id'] . '_' . $day['time'];
                            if (isset($keyedTodayList[$key])) {
                                $day['total'] += $keyedTodayList[$key]['total'];
                                unset($keyedTodayList[$key]);
                            }
                            $mergedList[] = $day;
                        }

                        // Add remaining today records if any
                        foreach ($keyedTodayList as $today) {
                            $mergedList[] = $today;
                        }

                        $dayRoomBill = $mergedList;
                    }
                }
            }


            if (!empty($dayRoomBill)) {
                foreach ($dayRoomBill as $k => $val) {
                    //                    $row = array_filter($list, function ($item) use ($val) {
//                        return $item['room_id'] == $val['room_id'];
//                    });
//                    $row = reset($row);
//                    $result['data'][$k]['useDiamondUserNum'] = $row ? $row['useDiamondUserNum'] : 0;
                    $result['data'][$k]['useDiamondUserNum'] = 0;
                    $dayRoomBill[$k]['roomName'] = '';
                    $dayRoomBill[$k]['unionName'] = '';
                    $dayRoomBill[$k]['useDiamondAll'] = $val['useDiamondAll'];
                    $dayRoomBill[$k]['roomId'] = $val['room_id'];
                    $dayRoomBill[$k]['unionId'] = $val['union_id'];
                    $dayRoomBill[$k]['created_date'] = $val['created_at'];
                    $dayRoomBill[$k]['useDiamondNumIos'] = 0;
                    $dayRoomBill[$k]['useDiamondNumAndroid'] = 0;

                    $dayRoomBill[$k]['roomName'] = $val['room_name'];
                    $dayRoomBill[$k]['roomUid'] = $val['room_uid'];
                    $dayRoomBill[$k]['unionName'] = $val['union_name'];
                }
            }

            $array = [
                'current_page' => $result['current_page'],
                'data' => $dayRoomBill,
                'first_page_url' => $result['first_page_url'],
                'from' => $result['from'],
                'last_page' => $result['last_page'],
                'last_page_url' => $result['last_page_url'],
                'next_page_url' => $result['next_page_url'],
                'path' => $result['path'],
                'per_page' => $result['per_page'],
                'prev_page_url' => $result['prev_page_url'],
                'to' => $result['to'],
                'total' => count($dayRoomBill)
            ];


            return $array;
        } else {
            return $this->getDaiylRoom2($page, $limit, $roomId, $startTime, $endTime, $unionId, $union_name, $room_name);
        }
    }


    /**
     * 获取每日房间流水数据(所有用户)
     * */
    public function getDaiylRoomAll($page, $limit, $roomUid = 0, $roomName = '', $unuserUid, $union_name, $startTime = '', $endTime = '')
    {
        $where = function ($q) use ($roomUid, $roomName, $unuserUid, $startTime, $endTime, $union_name) {
            //房间id
            if (!empty($roomUid)) {
                $roomInfo = DB::connection($this->connection)->table($this->roomTable)
                    ->where('room_uid', $roomUid)->first(['room_id']);
                $roomInfo = objToArray($roomInfo);
                $roomId = isset($roomInfo['room_id']) ? $roomInfo['room_id'] : 0;
                $q->where('room_id', $roomId);
            }

            //房间名称
            if (!empty($roomName)) {
                $q->where('room_name', 'like', $roomName . '%');
            }
            //公会长UID
            if (!empty($unuserUid)) {
                $userInfo = DB::connection($this->connection)->table($this->userTable)
                    ->where('uuid', $unuserUid)->first(['user_id']);
                $userInfo = objToArray($userInfo);
                $un_userId = isset($userInfo['user_id']) ? $userInfo['user_id'] : 0;
                $q->where('un_user_id', $un_userId);
            }
            //公会名称
            if (!empty($union_name)) {
                $q->where('union_name', 'like', $union_name . '%');
            }

            //开始时间
            if (!empty($startTime)) {
                $startDate = date('Y-m-d', strtotime($startTime));
                $q->where('date', '>=', $startDate);
            }
            //结束时间
            if (!empty($endTime)) {
                $endDate = date('Y-m-d', strtotime($endTime));
                $q->where('date', '<=', $endDate);
            }
        };

        $data = DB::table($this->dailyRoomDataTable)->where($where)->paginate($limit, ['*'], 'page', $page);
        $data = objToArray($data);

        if (!empty($data['data'])) {
            //查询公会长UID
            $unuserId = array_column($data['data'], 'un_user_id');
            $userInfos = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $unuserId)->get(['user_id', 'uuid']);
            $userInfos = objToArray($userInfos);
            $userInfos = array_column($userInfos, null, 'user_id');

            //重新整理数据
            foreach ($data['data'] as $k => &$v) {
                $v['union_uuid'] = isset($userInfos[$v['un_user_id']]) ? $userInfos[$v['un_user_id']]['uuid'] : 0;
                $v['date'] = Carbon::parse($v['date'])->toDateString();
            }
        }
        return $data;
    }

    //小游戏每日统计
    public function gameDayStatistics(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');

        $query = DB::connection($this->statCon)->table($this->gameDayStatistics_table);
        if (!empty($created_st)) {
            $created_st = date("Ymd", strtotime($created_st));
            $query = $query->where('ymd', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $created_ed = date("Ymd", strtotime($created_ed));
            $query = $query->where('ymd', '<=', $created_ed);
        }
        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        foreach ($result['data'] as &$val) {
            $val['second_all'] = round($val['second_all'] / 60, 2);
            $val['second_2'] = round($val['second_2'] / 60, 2);
            $val['second_3'] = round($val['second_3'] / 60, 2);
            $val['second_4'] = round($val['second_4'] / 60, 2);
        }

        return $result;
    }

    //小游戏每日房间统计
    public function gameRoomDayStatistics(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $roomId = $request->input('room_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');

        $query = DB::connection($this->statCon)->table($this->gameRoomDayStatistics_table);
        if (!empty($created_st)) {
            $created_st = date("Ymd", strtotime($created_st));
            $query = $query->where('ymd', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $created_ed = date("Ymd", strtotime($created_ed));
            $query = $query->where('ymd', '<=', $created_ed);
        }
        if (!empty($roomId)) {
            $query = $query->where('room_id', $roomId);
        }
        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        if (!empty($result['data'])) {
            $roomIds = array_column($result['data'], 'room_id');
            $roomList = DB::connection($this->connection)->table($this->roomTable)->whereIn('room_id', $roomIds)->select('room_id', 'name')->get();
            $roomList = array_column(objToArray($roomList), null, 'room_id');

            foreach ($result['data'] as &$val) {
                $val['roomName'] = empty($roomList[$val['room_id']]['name']) ? '' : $roomList[$val['room_id']]['name'];
            }
        }

        return $result;
    }

    //房间团战每日团战次数统计
    public function roomPkStatistics(Request $request)
    {
        $battleDurationAll = [5 * 60, 10 * 60, 15 * 60, 20 * 60, 25 * 60, 30 * 60, 35 * 60, 40 * 60, 45 * 60, 50 * 60];
        $roomId = $request->input('room_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');
        if (!empty($created_st)) {
            $created_st = date("Y-m-d H:i:s", strtotime($created_st));
        }
        if (!empty($created_ed)) {
            $created_ed = date("Y-m-d H:i:s", strtotime($created_ed));
        }

        $query = DB::connection($this->connection)->table($this->room_pk_table);
        if (!empty($created_st)) {
            $query = $query->where('created_at', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $query = $query->where('created_at', '<=', $created_ed);
        }
        if (!empty($roomId)) {
            $query = $query->where('room_id', $roomId);
        }
        $res1 = $query->groupBy('battle_duration')->selectRaw('count(1) as allNum, battle_duration')->get();
        $res1 = objToArray($res1);
        if (!empty($res1)) {
            $battleDurationAll = array_values(array_unique(array_merge($battleDurationAll, array_column($res1, 'battle_duration'))));
            sort($battleDurationAll);
        }
        $res1 = array_column($res1, 'allNum', 'battle_duration');

        $query = DB::connection($this->connection)->table($this->room_pk_table);
        $query = $query->where('status', 1);
        if (!empty($created_st)) {
            $query = $query->where('created_at', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $query = $query->where('created_at', '<=', $created_ed);
        }
        if (!empty($roomId)) {
            $query = $query->where('room_id', $roomId);
        }
        $res2 = $query->groupBy('battle_duration')->selectRaw('count(1) as successNum, battle_duration')->get();
        $res2 = objToArray($res2);
        $res2 = array_column($res2, 'successNum', 'battle_duration');

        $list = [];
        foreach ($battleDurationAll as $val) {
            $item = [
                'battle_duration' => $val,
                'allNum' => empty($res1[$val]) ? 0 : $res1[$val],
                'successNum' => empty($res2[$val]) ? 0 : $res2[$val],
            ];
            $item['failNum'] = $item['allNum'] - $item['successNum'];
            $list[] = $item;
        }

        return $list;
    }

    //房间团战pk详情查询
    public function roomPkList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $roomId = $request->input('room_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');
        if (!empty($created_st)) {
            $created_st = date("Y-m-d H:i:s", strtotime($created_st));
        }
        if (!empty($created_ed)) {
            $created_ed = date("Y-m-d H:i:s", strtotime($created_ed));
        }

        $query = DB::connection($this->connection)->table($this->room_pk_table);
        if (!empty($created_st)) {
            $query = $query->where('created_at', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $query = $query->where('created_at', '<=', $created_ed);
        }
        if (!empty($roomId)) {
            $query = $query->where('room_id', $roomId);
        }
        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        if (!empty($result['data'])) {
            $roomIds = array_column($result['data'], 'room_id');
            $roomList = DB::connection($this->connection)->table($this->roomTable)->whereIn('room_id', $roomIds)->select('room_id', 'name')->get();
            $roomList = array_column(objToArray($roomList), null, 'room_id');

            foreach ($result['data'] as &$val) {
                $val['roomName'] = empty($roomList[$val['room_id']]['name']) ? '' : $roomList[$val['room_id']]['name'];
                $val['true_battle_duration'] = empty($val['end_at']) ? 0 : ($val['end_at'] - $val['battle_start_at']);
            }
        }

        return $result;
    }

    //公会房间周结算列表
    public function unionRoomWeekRewardList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $showType = $request->input('show_type');//类型 0=已结算数据 1=本周即时统计 2=上周即时统计
        $roomId = $request->input('room_id');
        $unionId = $request->input('union_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');

        $list = [];
        $total = 0;
        //本周即时统计
        if ($showType == 1) {
            $day = getWeekTimestamp(0, 0);
            $time_st = $day[0] . ' 00:00:00';
            $time_ed = $day[1] . ' 23:59:59';
            // $time_st = '2025-01-20' . ' 00:00:00';
            // $time_ed = '2025-01-26' . ' 23:59:59';
            $list = $this->_getUnionRoomWeekReward($time_st, $time_ed);
            $list2 = [];
            foreach ($list as $val) {
                if (!empty($roomId)) {
                    if ($val['room_id'] != $roomId) {
                        continue;
                    }
                }
                if (!empty($unionId)) {
                    if ($val['union_id'] != $unionId) {
                        continue;
                    }
                }
                $list2[] = $val;
            }
            $list = $list2;
        }
        //上周即时统计
        if ($showType == 2) {
            $day = getWeekTimestamp(1, 0);
            $time_st = $day[0] . ' 00:00:00';
            $time_ed = $day[1] . ' 23:59:59';
            $list = $this->_getUnionRoomWeekReward($time_st, $time_ed);
            $list2 = [];
            foreach ($list as $val) {
                if (!empty($roomId)) {
                    if ($val['room_id'] != $roomId) {
                        continue;
                    }
                }
                if (!empty($unionId)) {
                    if ($val['union_id'] != $unionId) {
                        continue;
                    }
                }
                $list2[] = $val;
            }
            $list = $list2;
        }
        //已结算数据
        if ($showType == 0) {
            $query = DB::connection($this->connection)->table($this->unionRoomWeekrewardTable);
            if (!empty($created_st)) {
                $query = $query->where('created_at', '>=', $created_st);
            }
            if (!empty($created_ed)) {
                $query = $query->where('created_at', '<=', $created_ed);
            }
            if (!empty($roomId)) {
                $query = $query->where('room_id', $roomId);
            }
            if (!empty($unionId)) {
                $query = $query->where('union_id', $unionId);
            }
            $result = $query->paginate($limit, ['*'], 'page', $page);
            $result = objToArray($result);
            $list = empty($result['data']) ? [] : $result['data'];
            $total = empty($result['total']) ? 0 : $result['total'];
        }
        //数据整理
        if (!empty($list)) {
            $roomIds = array_column($list, 'room_id');
            $roomList = DB::connection($this->connection)->table($this->roomTable)->whereIn('room_id', $roomIds)->select('room_id', 'name', 'room_uid')->get();
            $roomList = array_column(objToArray($roomList), null, 'room_id');

            $unionIds = array_column($list, 'union_id');
            $unionList = DB::connection($this->connection)->table($this->unionTable)->select('id', 'name', 'uuid')->whereIn('id', $unionIds)->get();
            $unionList = array_column(objToArray($unionList), null, 'id');

            foreach ($list as $key => $val) {
                $val['room_name'] = empty($roomList[$val['room_id']]['name']) ? '' : $roomList[$val['room_id']]['name'];
                $val['room_uid'] = empty($roomList[$val['room_id']]['room_uid']) ? '' : $roomList[$val['room_id']]['room_uid'];

                $val['union_name'] = empty($unionList[$val['union_id']]['name']) ? '' : $unionList[$val['union_id']]['name'];
                $val['union_uuid'] = empty($unionList[$val['union_id']]['uuid']) ? '' : $unionList[$val['union_id']]['uuid'];

                $val['date'] = date("Y-m-d", strtotime($val['date']));
                $list[$key] = $val;
            }
        }

        $result = [
            'data' => $list,
            'total' => $total,
        ];
        return $result;
    }
    public function unionRoomWeekRewardListV2(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $showType = $request->input('show_type');//类型 0=已结算数据 1=本周即时统计 2=上周即时统计
        $roomId = $request->input('room_id');
        $unionId = $request->input('union_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');

        $list = [];
        $total = 0;
        //本周即时统计
        if ($showType == 1) {
            $day = getWeekTimestamp(0, 0);
            $time_st = $day[0] . ' 00:00:00';
            $time_ed = $day[1] . ' 23:59:59';
            // $time_st = '2025-01-20' . ' 00:00:00';
            // $time_ed = '2025-01-26' . ' 23:59:59';
            // $day[0] = '2025-01-20';
            // $day[1] = '2025-01-26';
            // Log::debug(['day0nn'=>$day[0]]);
            // Log::debug(['day1nn'=>$day[1]]);
            $list = $this->_getUnionRoomWeekRewardV2($time_st, $time_ed,$day[0],$day[1]);
            $list2 = [];
            foreach ($list as $val) {
                if (!empty($roomId)) {
                    if ($val['room_id'] != $roomId) {
                        continue;
                    }
                }
                if (!empty($unionId)) {
                    if ($val['union_id'] != $unionId) {
                        continue;
                    }
                }
                $list2[] = $val;
            }
            $list = $list2;
        }
        //上周即时统计
        if ($showType == 2) {
            $day = getWeekTimestamp(1, 0);
            // $day[0] = '2025-01-27';
            // $day[1] = '2025-02-02';
            $time_st = $day[0] . ' 00:00:00';
            $time_ed = $day[1] . ' 23:59:59';
            // Log::debug(['day0oo'=>$day[0]]);
            // Log::debug(['day1oo'=>$day[1]]);
            $list = $this->_getUnionRoomWeekRewardV2($time_st, $time_ed,$day[0],$day[1]);
            $list2 = [];
            foreach ($list as $val) {
                if (!empty($roomId)) {
                    if ($val['room_id'] != $roomId) {
                        continue;
                    }
                }
                if (!empty($unionId)) {
                    if ($val['union_id'] != $unionId) {
                        continue;
                    }
                }
                $list2[] = $val;
            }
            $list = $list2;
        }
        //已结算数据
        if ($showType == 0) {
            $query = DB::connection($this->connection)->table($this->unionRoomWeekrewardTable);
            if (!empty($created_st)) {
                $query = $query->where('created_at', '>=', $created_st);
            }
            if (!empty($created_ed)) {
                $query = $query->where('created_at', '<=', $created_ed);
            }
            if (!empty($roomId)) {
                $query = $query->where('room_id', $roomId);
            }
            if (!empty($unionId)) {
                $query = $query->where('union_id', $unionId);
            }
            $result = $query->paginate($limit, ['*'], 'page', $page);
            $result = objToArray($result);
            $list = empty($result['data']) ? [] : $result['data'];
            $total = empty($result['total']) ? 0 : $result['total'];
        }
        //数据整理
        if (!empty($list)) {
            $roomIds = array_column($list, 'room_id');
            $roomList = DB::connection($this->connection)->table($this->roomTable)->whereIn('room_id', $roomIds)->select('room_id', 'name', 'room_uid')->get();
            $roomList = array_column(objToArray($roomList), null, 'room_id');

            $unionIds = array_column($list, 'union_id');
            $unionList = DB::connection($this->connection)->table($this->unionTable)->select('id', 'name', 'uuid')->whereIn('id', $unionIds)->get();
            $unionList = array_column(objToArray($unionList), null, 'id');

            foreach ($list as $key => $val) {
                $val['room_name'] = empty($roomList[$val['room_id']]['name']) ? '' : $roomList[$val['room_id']]['name'];
                $val['room_uid'] = empty($roomList[$val['room_id']]['room_uid']) ? '' : $roomList[$val['room_id']]['room_uid'];

                $val['union_name'] = empty($unionList[$val['union_id']]['name']) ? '' : $unionList[$val['union_id']]['name'];
                $val['union_uuid'] = empty($unionList[$val['union_id']]['uuid']) ? '' : $unionList[$val['union_id']]['uuid'];

                $val['date'] = date("Y-m-d", strtotime($val['date']));
                $list[$key] = $val;
            }
        }

        $result = [
            'data' => $list,
            'total' => $total,
        ];
        return $result;
    }

    //公会房间周结算即时统计
    public function _getUnionRoomWeekReward($time_st, $time_ed)
    {
        $result = [];
        do {
            //所有联盟
            $unionList = DB::connection($this->connection)->table($this->unionTable)->where('stop', 1)->get();
            $unionList = objToArray($unionList);
            foreach ($unionList as $union) {
                //周结奖励
                if ($union['reward_type'] != 0) {
                    continue;
                }

                $effectiveRoom = [];//有效房间记录
                $stepRoom = [];//阶梯奖励记录
                $extraRoom = [];//额外奖励记录
                $inviteRoom = [];//会长邀请好友奖励
                $roomLogs = [];//记录房间信息
                //联盟房间
                $rooms = DB::connection($this->connection)->table($this->unionRoomTable)->where('un_id', $union['id'])
                    ->leftJoin("room", "room.room_id", "=", "union_room.room_id")
                    ->select('room.*')
                    ->get();
                $rooms = objToArray($rooms);
                if (empty($rooms)) {
                    continue;
                }
                //去除非BCD房间
                $roomList = [];
                foreach ($rooms as $room) {
                    if ($room['business_type'] == 1) {
                        $roomList[] = $room;
                    }
                }
                //获取房间收入
                $roomTotal = DB::connection($this->connection)->table($this->unionRoomStreamTable)
                    ->selectRaw('room_id, sum(data_value * data_num) as num')
                    ->where('created_at', '>=', $time_st)
                    ->where('created_at', '<=', $time_ed)
                    ->where('union_id', $union['id'])
                    ->groupBy("room_id")
                    ->get();
                Log::debug(["oo"=>$roomTotal]);
                $roomTotal = objToArray($roomTotal);
                $roomTotal = array_column($roomTotal, 'num', 'room_id');
                //房间流水合计
                $unionTotalSum = 0;
                foreach ($roomList as $room) {
                    if ($room['business_type'] != 1) {
                        continue;
                    }
                    //房间周消费钻石数
                    $totalSum = empty($roomTotal[$room['room_id']]) ? 0 : $roomTotal[$room['room_id']];
                    $unionTotalSum = $unionTotalSum + $totalSum;
                }

                //阶梯奖励
                //大于等于20万钻石（1%）    大于等于80万钻石（1.5%）    大于等于200万钻石（3%）
                if ($unionTotalSum < 200000) {
                    $rate = 0;
                } elseif ($unionTotalSum >= 200000 && $unionTotalSum < 500000) {
                    $rate = 0.01;
                } elseif ($unionTotalSum >= 500000 && $unionTotalSum < 1000000) {
                    $rate = 0.02;
                } elseif ($unionTotalSum >= 1000000) {
                    $rate = 0.03;
                }
                $roomTotal = intval($unionTotalSum * $rate);
                $stepRate = round($rate * 100);

                //日志记录
                $item = [];
                $item['date'] = date("Ymd", strtotime($time_st));
                $item['union_id'] = $union['id'];
                $item['room_id'] = 0;
                $item['roomTotal'] = $unionTotalSum;
                $item['stepRate'] = $stepRate;
                $item['stepTotal'] = $roomTotal;
                $item['extraRate'] = 0;
                $item['extraTotal'] = 0;
                $item['inviteTotal'] = 0;
                $item['inviteRewardTotal'] = 0;
                $item['created_at'] = '';

                if ($item['roomTotal'] > 0) {
                    $result[] = $item;
                }
                /*
                //阶梯奖励
                foreach($roomList as $room) {
                    if($room['business_type'] != 1) {
                        continue;
                    }
                    //房间周消费钻石数
                    $totalSum = empty($roomTotal[$room['room_id']]) ? 0 : $roomTotal[$room['room_id']];
                    //大于等于20万钻石（1%）    大于等于80万钻石（1.5%）    大于等于200万钻石（3%）
                    if($totalSum < 200000){
                        $rate = 0;
                    }elseif($totalSum>=200000 && $totalSum<800000){
                        $rate = 0.01;
                    }elseif($totalSum>=800000 && $totalSum<2000000){
                        $rate = 0.015;
                    }elseif($totalSum >= 2000000){
                        $rate = 0.03;
                    }
                    $stepRoom[$room['room_id']] = intval($totalSum*$rate);
                    $roomLogs[$room['room_id']]['stepRate'] = round($rate*100);
                    $roomLogs[$room['room_id']]['stepTotal'] = $stepRoom[$room['room_id']];
                }

                //日志记录
                foreach($roomList as $room) {
                    $item = [];
                    $item['id'] = 0;
                    $item['date'] = date("Ymd", strtotime($time_st));
                    $item['union_id'] = $union['id'];
                    $item['room_id'] = $room['room_id'];
                    $item['roomTotal'] = empty($roomTotal[$room['room_id']]) ? 0 : $roomTotal[$room['room_id']];
                    $item['stepRate'] = empty($roomLogs[$room['room_id']]['stepRate']) ? 0 : $roomLogs[$room['room_id']]['stepRate'];
                    $item['stepTotal'] = empty($roomLogs[$room['room_id']]['stepTotal']) ? 0 : $roomLogs[$room['room_id']]['stepTotal'];
                    $item['extraRate'] = empty($roomLogs[$room['room_id']]['extraRate']) ? 0 : $roomLogs[$room['room_id']]['extraRate'];
                    $item['extraTotal'] = empty($roomLogs[$room['room_id']]['extraTotal']) ? 0 : $roomLogs[$room['room_id']]['extraTotal'];
                    $item['inviteTotal'] = empty($roomLogs[$room['room_id']]['inviteTotal']) ? 0 : $roomLogs[$room['room_id']]['inviteTotal'];
                    $item['inviteRewardTotal'] = empty($roomLogs[$room['room_id']]['inviteRewardTotal']) ? 0 : $roomLogs[$room['room_id']]['inviteRewardTotal'];
                    $item['created_at'] = '';

                    if($item['roomTotal'] > 0) {
                        $result[] = $item;
                    }
                }
                */
            }
        } while (false);

        return $result;
    }
    public function _getUnionRoomWeekRewardV2($time_st, $time_ed, $date_st, $date_ed)
    {
        $result = [];
        do {
            //所有联盟
            $unionList = DB::connection($this->connection)->table($this->unionTable)->where('stop', 1)->get();
            $unionList = objToArray($unionList);
            foreach ($unionList as $union) {
                //周结奖励
                if ($union['reward_type'] != 0) {
                    continue;
                }

                $effectiveRoom = [];//有效房间记录
                $stepRoom = [];//阶梯奖励记录
                $extraRoom = [];//额外奖励记录
                $inviteRoom = [];//会长邀请好友奖励
                $roomLogs = [];//记录房间信息
                //联盟房间
                $rooms = DB::connection($this->connection)->table($this->unionRoomTable)->where('un_id', $union['id'])
                    ->leftJoin("room", "room.room_id", "=", "union_room.room_id")
                    ->select('room.*')
                    ->get();
                $rooms = objToArray($rooms);
                if (empty($rooms)) {
                    continue;
                }
                //去除非BCD房间
                $roomList = [];
                foreach ($rooms as $room) {
                    if ($room['business_type'] == 1 || true) {
                    // if ($room['business_type'] == 1) {
                        $roomList[] = $room;
                    }
                }
                $unionId = $union['id'];
                $query = null;
                if ($date_st != $date_ed) { //多天
                    //包含今天
                    if (strtotime($date_ed) >= strtotime(date('Y-m-d'))) {
                        $Today = date("Y-m-d");
                        $endTime = date("Y-m-d", strtotime("yesterday"));
                        $query = DB::connection($this->connection)
                            ->query()
                            ->fromSub(function ($query) use ($Today, $date_st, $endTime, $unionId) {
                                $query1 = DB::connection($this->connection)->table($this->unionRoomStreamTable . ' as a')
                                    ->selectRaw('zb_a.room_id, sum(zb_a.data_value * zb_a.data_num) as num')
                                    ->where('a.union_id', $unionId)
                                    ->whereRaw('zb_a.data_type in (1,2)')
                                    ->whereRaw('zb_a.created_at <= ? AND zb_a.created_at >= ?', [date("Y-m-d 23:59:59", strtotime($Today)), date("Y-m-d 00:00:00", strtotime($Today))])
                                    ->groupBy('a.room_id');
                                    // ->groupBy(DB::raw('DATE_FORMAT(zb_a.created_at, "%Y-%m-%d")'));


                                $query2 = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                                    ->selectRaw('zb_a.room_id, sum(zb_a.total) as num')
                                    ->where('a.union_id', $unionId)
                                    ->whereRaw('zb_a.created_at <= ? AND zb_a.created_at >= ?', [date("Y-m-d", strtotime($endTime)), date("Y-m-d", strtotime($date_st))])
                                    ->groupBy('a.room_id');
                                    // ->groupBy('a.created_at');
                                $query->fromSub($query1, 'u1')
                                    ->unionAll($query2);
                            }, 'a');

                    } else {//不包含今天
                        $query = DB::connection($this->connection)->table($this->unionRoomStreamTableDays . ' as a')
                            ->selectRaw('zb_a.room_id, sum(zb_a.total) as num')
                            ->where('a.union_id', $unionId)
                            ->where('a.created_at', '<=', date("Y-m-d", strtotime($date_ed)))
                            ->where('a.created_at', '>=', date("Y-m-d", strtotime($date_st)))
                            ->groupBy('a.room_id');
                        // Log::debug("kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk\n");
                    }
                } else { //单天

                }
                $roomTotal = $query->get();
                // Log::debug(["nn"=>$roomTotal]);
                // //获取房间收入
                // $roomTotal = DB::connection($this->connection)->table($this->unionRoomStreamTable)
                //     ->selectRaw('room_id, sum(data_value * data_num) as num')
                //     ->where('created_at', '>=', $time_st)
                //     ->where('created_at', '<=', $time_ed)
                //     ->where('union_id', $union['id'])
                //     ->groupBy("room_id")
                //     ->get();

                $roomTotal = objToArray($roomTotal);
                $roomTotal = array_column($roomTotal, 'num', 'room_id');

                //房间流水合计
                $unionTotalSum = 0;
                foreach ($roomList as $room) {
                    if ($room['business_type'] != 1 && false) {
                    // if ($room['business_type'] != 1) {
                        continue;
                    }
                    //房间周消费钻石数
                    $totalSum = empty($roomTotal[$room['room_id']]) ? 0 : $roomTotal[$room['room_id']];
                    $unionTotalSum = $unionTotalSum + $totalSum;
                }

                //阶梯奖励
                //大于等于20万钻石（1%）    大于等于80万钻石（1.5%）    大于等于200万钻石（3%）
                if ($unionTotalSum < 200000) {
                    $rate = 0;
                } elseif ($unionTotalSum >= 200000 && $unionTotalSum < 500000) {
                    $rate = 0.01;
                } elseif ($unionTotalSum >= 500000 && $unionTotalSum < 1000000) {
                    $rate = 0.02;
                } elseif ($unionTotalSum >= 1000000) {
                    $rate = 0.03;
                }
                $roomTotal = intval($unionTotalSum * $rate);
                $stepRate = round($rate * 100);

                //日志记录
                $item = [];
                $item['date'] = date("Ymd", strtotime($time_st));
                $item['union_id'] = $union['id'];
                $item['room_id'] = 0;
                $item['roomTotal'] = $unionTotalSum;
                $item['stepRate'] = $stepRate;
                $item['stepTotal'] = $roomTotal;
                $item['extraRate'] = 0;
                $item['extraTotal'] = 0;
                $item['inviteTotal'] = 0;
                $item['inviteRewardTotal'] = 0;
                $item['created_at'] = '';

                if ($item['roomTotal'] > 0) {
                    $result[] = $item;
                }
                /*
                //阶梯奖励
                foreach($roomList as $room) {
                    if($room['business_type'] != 1) {
                        continue;
                    }
                    //房间周消费钻石数
                    $totalSum = empty($roomTotal[$room['room_id']]) ? 0 : $roomTotal[$room['room_id']];
                    //大于等于20万钻石（1%）    大于等于80万钻石（1.5%）    大于等于200万钻石（3%）
                    if($totalSum < 200000){
                        $rate = 0;
                    }elseif($totalSum>=200000 && $totalSum<800000){
                        $rate = 0.01;
                    }elseif($totalSum>=800000 && $totalSum<2000000){
                        $rate = 0.015;
                    }elseif($totalSum >= 2000000){
                        $rate = 0.03;
                    }
                    $stepRoom[$room['room_id']] = intval($totalSum*$rate);
                    $roomLogs[$room['room_id']]['stepRate'] = round($rate*100);
                    $roomLogs[$room['room_id']]['stepTotal'] = $stepRoom[$room['room_id']];
                }

                //日志记录
                foreach($roomList as $room) {
                    $item = [];
                    $item['id'] = 0;
                    $item['date'] = date("Ymd", strtotime($time_st));
                    $item['union_id'] = $union['id'];
                    $item['room_id'] = $room['room_id'];
                    $item['roomTotal'] = empty($roomTotal[$room['room_id']]) ? 0 : $roomTotal[$room['room_id']];
                    $item['stepRate'] = empty($roomLogs[$room['room_id']]['stepRate']) ? 0 : $roomLogs[$room['room_id']]['stepRate'];
                    $item['stepTotal'] = empty($roomLogs[$room['room_id']]['stepTotal']) ? 0 : $roomLogs[$room['room_id']]['stepTotal'];
                    $item['extraRate'] = empty($roomLogs[$room['room_id']]['extraRate']) ? 0 : $roomLogs[$room['room_id']]['extraRate'];
                    $item['extraTotal'] = empty($roomLogs[$room['room_id']]['extraTotal']) ? 0 : $roomLogs[$room['room_id']]['extraTotal'];
                    $item['inviteTotal'] = empty($roomLogs[$room['room_id']]['inviteTotal']) ? 0 : $roomLogs[$room['room_id']]['inviteTotal'];
                    $item['inviteRewardTotal'] = empty($roomLogs[$room['room_id']]['inviteRewardTotal']) ? 0 : $roomLogs[$room['room_id']]['inviteRewardTotal'];
                    $item['created_at'] = '';

                    if($item['roomTotal'] > 0) {
                        $result[] = $item;
                    }
                }
                */
            }
        } while (false);

        return $result;
    }

    //娱乐等级整体数据
    public function gameRankStat(Request $request)
    {
        //侠客
        $res1 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 100)
            ->where('game_balance', '<=', 999)
            ->count();
        //大侠
        $res2 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 1000)
            ->where('game_balance', '<=', 3999)
            ->count();
        //掌门
        $res3 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 4000)
            ->where('game_balance', '<=', 15999)
            ->count();
        //宗师
        $res4 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 16000)
            ->where('game_balance', '<=', 62999)
            ->count();
        //盟主
        $res5 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 63000)
            ->where('game_balance', '<=', 249999)
            ->count();
        //国王
        $res6 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 250000)
            ->where('game_balance', '<=', 999999)
            ->count();
        //大帝
        $res7 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 1000000)
            ->where('game_balance', '<=', 2999999)
            ->count();
        //天神
        $res8 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 3000000)
            ->where('game_balance', '<=', 9999999)
            ->count();
        //天尊
        $res9 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->where('game_balance', '>=', 10000000)
            ->count();

        $result = [];
        $result['rank_1'] = empty($res1) ? 0 : $res1;
        $result['rank_2'] = empty($res2) ? 0 : $res2;
        $result['rank_3'] = empty($res3) ? 0 : $res3;
        $result['rank_4'] = empty($res4) ? 0 : $res4;
        $result['rank_5'] = empty($res5) ? 0 : $res5;
        $result['rank_6'] = empty($res6) ? 0 : $res6;
        $result['rank_7'] = empty($res7) ? 0 : $res7;
        $result['rank_8'] = empty($res8) ? 0 : $res8;
        $result['rank_9'] = empty($res9) ? 0 : $res9;

        return $result;
    }

    //娱乐等级奖励下发明细
    public function gameRankRewardStat(Request $request)
    {
        $levels = [6, 11, 16, 21, 26, 31, 36, 41, 45];
        $result = [];

        foreach ($levels as $level) {
            $userNum = DB::connection($this->connection)->table($this->gameRankRewardLogTable)
                ->where('level', $level)
                ->count();
            $rewardNum = DB::connection($this->connection)->table($this->gameRankRewardLogTable)
                ->where('level', $level)
                ->where('if_active_item', 1)
                ->count();

            $rank = DB::connection($this->connection)->table($this->financeGameRankTable)
                ->where('level', $level)->first();
            $rank = objToArray($rank);

            $giftPrice = 0;
            if (!empty($rank['active_item'])) {
                $activeItem = json_decode($rank['active_item'], true);
                foreach ($activeItem as $val) {
                    if ($val['data_type'] == 1) {
                        $gift = DB::connection($this->connection)->table($this->giftTableV2)
                            ->where('gift_id', $val['data_id'])
                            ->first();
                        $giftPrice = empty($gift->price) ? 0 : $gift->price;
                    }
                }
            }

            $item = [
                'level' => $level,
                'rank_name' => empty($rank['rank_name']) ? '' : $rank['rank_name'],
                'userNum' => (int) $userNum,
                'rewardNum' => (int) $rewardNum,
                'giftTotal' => $rewardNum * $giftPrice,
            ];
            $result[] = $item;
        }

        return $result;
    }


    //幸运礼物统计数据概述
    public function luckGiftAllStatistics(Request $request)
    {
        $giftId = $request->input('gift_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');

        $giftList = DB::connection($this->connection)->table($this->giftTable)->where('gift_type', 2)->get();
        $giftList = array_column(objToArray($giftList), null, 'gift_id');

        $query = DB::connection($this->connection)->table($this->giftLuckLogTable);
        if (!empty($created_st)) {
            $query = $query->where('created_at', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $query = $query->where('created_at', '<=', $created_ed);
        }
        if (!empty($giftId)) {
            $query = $query->where('gift_id', $giftId);
        }
        $res1 = $query->selectRaw('count(DISTINCT user_id) as userNum, sum(diamond_num) as rewardNum')
            ->first();
        $res1 = objToArray($res1);

        $query = DB::connection($this->connection)->table($this->giftStreamTable);
        if (!empty($created_st)) {
            $query = $query->where('change_timestamp', '>=', strtotime($created_st));
        }
        if (!empty($created_ed)) {
            $query = $query->where('change_timestamp', '<=', strtotime($created_ed));
        }
        if (!empty($giftId)) {
            $query = $query->where('gift_id', $giftId);
        } else {
            $query = $query->whereIn('gift_id', array_column($giftList, 'gift_id'));
        }
        $res2 = $query->selectRaw('sum(gift_price*gift_num) as divideNum')
            ->first();
        $res2 = objToArray($res2);

        $result = [];
        //人数
        $result['userNum'] = empty($res1['userNum']) ? 0 : $res1['userNum'];
        //消耗钻石数
        $result['useNum'] = empty($res2['divideNum']) ? 0 : $res2['divideNum'] * 10;
        //产出钻石数
        $result['rewardNum'] = empty($res1['rewardNum']) ? 0 : (int) $res1['rewardNum'];
        //主播分成钻石数
        $result['divideNum'] = empty($res2['divideNum']) ? 0 : (int) $res2['divideNum'];
        //利润钻石数
        $result['profitNum'] = $result['useNum'] - $result['rewardNum'] - $result['divideNum'];
        //利润元
        $result['profitYun'] = round($result['profitNum'] / 10, 2);
        //利润率
        $result['profitRate'] = (($result['useNum'] - $result['divideNum']) != 0) ? round(($result['profitNum'] / ($result['useNum'] - $result['divideNum'])) * 100, 2) : 0;

        return $result;
    }

    //幸运礼物统计用户统计
    public function luckGiftUserStatistics(Request $request)
    {
        $giftId = $request->input('gift_id');
        $userId = $request->input('user_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');

        $giftList = DB::connection($this->connection)->table($this->giftTable)->where('gift_type', 2)->get();
        $giftList = array_column(objToArray($giftList), null, 'gift_id');

        $query = DB::connection($this->connection)->table($this->giftLuckLogTable);
        if (!empty($created_st)) {
            $query = $query->where('created_at', '>=', $created_st);
        }
        if (!empty($created_ed)) {
            $query = $query->where('created_at', '<=', $created_ed);
        }
        if (!empty($giftId)) {
            $query = $query->where('gift_id', $giftId);
        }
        if (!empty($userId)) {
            $query = $query->where('user_id', $userId);
        }
        $res1 = $query->selectRaw('sum(diamond_num) as rewardNum,user_id')
            ->groupBy('user_id')
            ->get();
        $res1 = objToArray($res1);

        $query = DB::connection($this->connection)->table($this->giftStreamTable);
        if (!empty($created_st)) {
            $query = $query->where('change_timestamp', '>=', strtotime($created_st));
        }
        if (!empty($created_ed)) {
            $query = $query->where('change_timestamp', '<=', strtotime($created_ed));
        }
        if (!empty($giftId)) {
            $query = $query->where('gift_id', $giftId);
        } else {
            $query = $query->whereIn('gift_id', array_column($giftList, 'gift_id'));
        }
        if (!empty($userId)) {
            $query = $query->where('from_user_id', $userId);
        }
        $res2 = $query->selectRaw('from_user_id, sum(gift_price*gift_num) as divideNum')
            ->groupBy('from_user_id')
            ->get();
        $res2 = objToArray($res2);

        $userIds = [];
        $result = [];
        foreach ($res1 as $val) {
            $result[$val['user_id']]['user_id'] = $val['user_id'];
            //产出钻石数
            $result[$val['user_id']]['rewardNum'] = empty($val['rewardNum']) ? 0 : (int) $val['rewardNum'];
            $userIds[] = $val['user_id'];
        }
        foreach ($res2 as $val) {
            $result[$val['from_user_id']]['user_id'] = $val['from_user_id'];
            //主播分成钻石数
            $result[$val['from_user_id']]['divideNum'] = empty($val['divideNum']) ? 0 : (int) $val['divideNum'];
            //消耗钻石数
            $result[$val['from_user_id']]['useNum'] = empty($val['divideNum']) ? 0 : (int) $val['divideNum'] * 10;
            $userIds[] = $val['from_user_id'];
        }
        $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id', 'nickname', 'uuid')->get();
        $userList = array_column(objToArray($userList), null, 'user_id');


        foreach ($result as $key => $val) {
            //利润钻石数
            $val['profitNum'] = $val['rewardNum'] - $val['useNum'] + $val['divideNum'];
            $val['nickname'] = empty($userList[$val['user_id']]['nickname']) ? '' : $userList[$val['user_id']]['nickname'];
            $val['uuid'] = empty($userList[$val['user_id']]['uuid']) ? '' : $userList[$val['user_id']]['uuid'];

            $result[$key] = $val;
        }

        return array_values($result);
    }

    //幸运礼物统计抽奖记录
    public function luckGiftDrawLog(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $giftId = $request->input('gift_id');
        $userId = $request->input('user_id');
        $created_st = $request->input('created_st');
        $created_ed = $request->input('created_ed');
        $giftName = $request->input('gift_name');

        $query = DB::connection($this->connection)->table($this->giftStreamTable)->where('gift_type', 2)->orderBy('change_timestamp', 'desc');
        if (!empty($created_st)) {
            $query = $query->where('change_timestamp', '>=', strtotime($created_st));
        }
        if (!empty($created_ed)) {
            $query = $query->where('change_timestamp', '<=', strtotime($created_ed));
        }
        if (!empty($giftId)) {
            $query = $query->where('gift_id', $giftId);
        }
        if (!empty($userId)) {
            $query = $query->where('from_user_id', $userId);
        }
        if (!empty($giftName)) {
            $giftIds = DB::connection($this->connection)->table($this->giftTable)->where('name', 'like', '%' . $giftName . '%')->select('gift_id')->get();
            $giftIds = array_column(objToArray($giftIds), 'gift_id');
            if (!empty($giftIds)) {
                $query = $query->whereIn('gift_id', $giftIds);
            }
        }
        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        if (!empty($result['data'])) {
            $userIds = array_column($result['data'], 'from_user_id');
            $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->get();
            $userList = array_column(objToArray($userList), null, 'user_id');

            $streamIds = array_column($result['data'], 'stream_id');
            $streamList = DB::connection($this->connection)->table($this->giftLuckLogTable)
                ->whereIn('stream_id', $streamIds)
                ->groupBy('stream_id')
                ->selectRaw('stream_id, sum(diamond_num) as diamond_num')
                ->get();
            $streamList = array_column(objToArray($streamList), 'diamond_num', 'stream_id');

            foreach ($result['data'] as $key => $val) {
                $item = [];
                $item['stream_id'] = $val['stream_id'];
                $item['user_id'] = $val['from_user_id'];
                $item['nickname'] = empty($userList[$val['from_user_id']]['nickname']) ? '' : $userList[$val['from_user_id']]['nickname'];
                $item['uuid'] = empty($userList[$val['from_user_id']]['uuid']) ? '' : $userList[$val['from_user_id']]['uuid'];
                $item['gift_id'] = $val['gift_id'];
                $item['gift_name'] = $val['gift_name'];
                $item['gift_price'] = $val['gift_price'];
                $item['gift_num'] = $val['gift_num'];
                $item['change_timestamp'] = $val['change_timestamp'];
                $item['diamond_num'] = empty($streamList[$val['stream_id']]) ? 0 : $streamList[$val['stream_id']];

                $result['data'][$key] = $item;
            }
        }

        return $result;

    }

    //幸运礼物统计抽奖记录明细
    public function luckGiftDraInfoList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $streamId = $request->input('stream_id');

        $query = DB::connection($this->connection)->table($this->giftLuckLogTable)->where('stream_id', $streamId);
        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);

        return $result;

    }

    //极妙抽奖奖池统计
    public function getJmRewardPool($data)
    {
        $res = curl_request(getKkaudioActivityUrl() . '/jm/getRewardPool', $data, 'urlencoded', 'GET');
        if (!isset($res['code']))
            $res['code'] = 50000;
        if (!isset($res['msg']))
            $res['msg'] = "系统异常";
        if (!isset($res['data']))
            $res['data'] = [];
        return $res;
    }

    //极妙抽奖抽奖记录
    public function getJmRewardList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $openid = $request->get('openid', '');
        $money = $request->get('money', 0);
        $timeSt = $request->get('timeSt', 0);
        $timeEd = $request->get('timeEd', 0);

        $query = DB::connection($this->activityConn)->table($this->jmDrawQrcodeTable)->where('status', 1);
        if (!empty($openid)) {
            $query = $query->where('openid', $openid);
        }
        if (!empty($money)) {
            $query = $query->where('money', $money);
        }
        if (!empty($timeSt)) {
            $query = $query->where('d_time', '>=', $timeSt);
        }
        if (!empty($timeEd)) {
            $query = $query->where('d_time', '<=', $timeEd);
        }

        $result = $query->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);

        return $result;

    }

    //2023疯狂双十一贝壳礼包数据概述
    public function ssy2023ShellBagStat(Request $request)
    {
        $res1 = DB::connection($this->activityConn)->table($this->ssy2023EventTable)
            ->where('type', 'recharge')
            ->selectRaw('ymd,sum(value1) as shellNum')
            ->groupBy('ymd')
            ->get();
        $res1 = objToArray($res1);
        foreach ($res1 as $key => $val) {
            $res1[$key]['ymd'] = strtotime($val['ymd']);
        }
        $res1 = array_column($res1, null, 'ymd');

        $res2 = DB::connection($this->activityConn)->table($this->ssy2023ShellTable)
            ->selectRaw('ymd,sum(bag_num_1) as bag_num_1,sum(reward_diamand_num_1) as reward_diamand_num_1,sum(bag_num_2) as bag_num_2,sum(reward_diamand_num_2) as reward_diamand_num_2,sum(bag_num_3) as bag_num_3,sum(reward_diamand_num_3) as reward_diamand_num_3')
            ->groupBy('ymd')
            ->get();
        $res2 = objToArray($res2);
        foreach ($res2 as $key => $val) {
            $res2[$key]['ymd'] = strtotime($val['ymd']);
        }
        $res2 = array_column($res2, null, 'ymd');

        $ymdList = array_unique(array_merge(array_column($res1, 'ymd'), array_column($res2, 'ymd')));
        rsort($ymdList);
        $list = [];
        foreach ($ymdList as $ymd) {
            $item = [];
            $item['ymd'] = date("Y-m-d", $ymd);
            $item['shellNum'] = empty($res1[$ymd]['shellNum']) ? 0 : $res1[$ymd]['shellNum'];
            $item['bag_num_1'] = empty($res2[$ymd]['bag_num_1']) ? 0 : $res2[$ymd]['bag_num_1'];
            $item['reward_diamand_num_1'] = empty($res2[$ymd]['reward_diamand_num_1']) ? 0 : $res2[$ymd]['reward_diamand_num_1'];
            $item['bag_num_2'] = empty($res2[$ymd]['bag_num_2']) ? 0 : $res2[$ymd]['bag_num_2'];
            $item['reward_diamand_num_2'] = empty($res2[$ymd]['reward_diamand_num_2']) ? 0 : $res2[$ymd]['reward_diamand_num_2'];
            $item['bag_num_3'] = empty($res2[$ymd]['bag_num_3']) ? 0 : $res2[$ymd]['bag_num_3'];
            $item['reward_diamand_num_3'] = empty($res2[$ymd]['reward_diamand_num_3']) ? 0 : $res2[$ymd]['reward_diamand_num_3'];

            $list[] = $item;
        }

        return $list;
    }

    //2023疯狂双十一折扣礼包数据概述
    public function ssy2023RateBagStat(Request $request)
    {
        $res1 = DB::connection($this->activityConn)->table($this->ssy2023EventTable)
            ->where('type', 'buyRateBag')
            ->selectRaw('ymd,value1,count(DISTINCT user_id) as useNum, count(1) as buyNum')
            ->groupBy('ymd', 'value1')
            ->get();
        $res1 = objToArray($res1);

        $list = [];
        foreach ($res1 as $val) {
            $list[strtotime($val['ymd'])]['ymd'] = $val['ymd'];
            $list[strtotime($val['ymd'])]['useNum_' . $val['value1']] = $val['useNum'];
            $list[strtotime($val['ymd'])]['buyNum_' . $val['value1']] = $val['buyNum'];
        }
        rsort($list);

        foreach ($list as $key => $val) {
            $val['useNum_1'] = empty($val['useNum_1']) ? 0 : $val['useNum_1'];
            $val['buyNum_1'] = empty($val['buyNum_1']) ? 0 : $val['buyNum_1'];
            $val['useNum_2'] = empty($val['useNum_2']) ? 0 : $val['useNum_2'];
            $val['buyNum_2'] = empty($val['buyNum_2']) ? 0 : $val['buyNum_2'];
            $val['useNum_3'] = empty($val['useNum_3']) ? 0 : $val['useNum_3'];
            $val['buyNum_3'] = empty($val['buyNum_3']) ? 0 : $val['buyNum_3'];
            $list[$key] = $val;
        }

        return $list;
    }

    //2023疯狂双十一折扣礼物数据概述
    public function ssy2023GiftStat(Request $request)
    {
        //活动开始日期
        $finalsTimeSt = '1699459200';//2023-11-09 00:00:00
        //活动结束日期
        $finalsTimeEt = '1699718399';//2023-11-11 23:59:59

        $res1 = DB::connection($this->connection)->table($this->giftStreamTable)
            ->where('change_timestamp', '>=', $finalsTimeSt)
            ->where('change_timestamp', '<=', $finalsTimeEt)
            ->whereIn('gift_id', [7, 141, 31, 138])
            ->selectRaw("gift_id,from_unixtime(change_timestamp, '%Y-%m-%d') as ymd,count(DISTINCT from_user_id) as useNum, count(stream_id) as sendNum,sum(gift_price*gift_num) as diamandNum")
            ->groupBy('ymd', 'gift_id')
            ->get();
        $res1 = objToArray($res1);
        $list = [];
        foreach ($res1 as $val) {
            $list[$val['ymd']][$val['gift_id']] = $val;
        }

        $listEx = [];
        foreach ($list as $key => $val) {
            $item = [];
            //日期
            $item['ymd'] = $key;

            //宇宙嘉年华
            //送出人数
            $item['useNum_7'] = empty($val[7]['useNum']) ? 0 : $val[7]['useNum'];
            //送出次数
            $item['sendNum_7'] = empty($val[7]['sendNum']) ? 0 : $val[7]['sendNum'];
            //总计钻石数
            $item['diamandNum_7'] = empty($val[7]['diamandNum']) ? 0 : $val[7]['diamandNum'];

            //人间理想
            //送出人数
            $item['useNum_141'] = empty($val[141]['useNum']) ? 0 : $val[141]['useNum'];
            //送出次数
            $item['sendNum_141'] = empty($val[141]['sendNum']) ? 0 : $val[141]['sendNum'];
            //总计钻石数
            $item['diamandNum_141'] = empty($val[141]['diamandNum']) ? 0 : $val[141]['diamandNum'];

            //为你摘星
            //送出人数
            $item['useNum_31'] = empty($val[31]['useNum']) ? 0 : $val[31]['useNum'];
            //送出次数
            $item['sendNum_31'] = empty($val[31]['sendNum']) ? 0 : $val[31]['sendNum'];
            //总计钻石数
            $item['diamandNum_31'] = empty($val[31]['diamandNum']) ? 0 : $val[31]['diamandNum'];

            //星辰大海
            //送出人数
            $item['useNum_138'] = empty($val[138]['useNum']) ? 0 : $val[138]['useNum'];
            //送出次数
            $item['sendNum_138'] = empty($val[138]['sendNum']) ? 0 : $val[138]['sendNum'];
            //总计钻石数
            $item['diamandNum_138'] = empty($val[138]['diamandNum']) ? 0 : $val[138]['diamandNum'];

            $listEx[] = $item;
        }

        return $listEx;
    }

    //2023双12贝壳礼包数据概述
    public function sse2023ShellBagStat(Request $request)
    {
        $res1 = DB::connection($this->activityConn)->table($this->sse2023EventTable)
            ->where('type', 'recharge')
            ->selectRaw('ymd,sum(value1) as shellNum')
            ->groupBy('ymd')
            ->get();
        $res1 = objToArray($res1);
        foreach ($res1 as $key => $val) {
            $res1[$key]['ymd'] = strtotime($val['ymd']);
        }
        $res1 = array_column($res1, null, 'ymd');

        $res2 = DB::connection($this->activityConn)->table($this->sse2023ShellTable)
            ->selectRaw('ymd,sum(bag_num_1) as bag_num_1,sum(reward_diamand_num_1) as reward_diamand_num_1,sum(bag_num_2) as bag_num_2,sum(reward_diamand_num_2) as reward_diamand_num_2,sum(bag_num_3) as bag_num_3,sum(reward_diamand_num_3) as reward_diamand_num_3')
            ->groupBy('ymd')
            ->get();
        $res2 = objToArray($res2);
        foreach ($res2 as $key => $val) {
            $res2[$key]['ymd'] = strtotime($val['ymd']);
        }
        $res2 = array_column($res2, null, 'ymd');

        $ymdList = array_unique(array_merge(array_column($res1, 'ymd'), array_column($res2, 'ymd')));
        rsort($ymdList);
        $list = [];
        foreach ($ymdList as $ymd) {
            $item = [];
            $item['ymd'] = date("Y-m-d", $ymd);
            $item['shellNum'] = empty($res1[$ymd]['shellNum']) ? 0 : $res1[$ymd]['shellNum'];
            $item['bag_num_1'] = empty($res2[$ymd]['bag_num_1']) ? 0 : $res2[$ymd]['bag_num_1'];
            $item['reward_diamand_num_1'] = empty($res2[$ymd]['reward_diamand_num_1']) ? 0 : $res2[$ymd]['reward_diamand_num_1'];
            $item['bag_num_2'] = empty($res2[$ymd]['bag_num_2']) ? 0 : $res2[$ymd]['bag_num_2'];
            $item['reward_diamand_num_2'] = empty($res2[$ymd]['reward_diamand_num_2']) ? 0 : $res2[$ymd]['reward_diamand_num_2'];
            $item['bag_num_3'] = empty($res2[$ymd]['bag_num_3']) ? 0 : $res2[$ymd]['bag_num_3'];
            $item['reward_diamand_num_3'] = empty($res2[$ymd]['reward_diamand_num_3']) ? 0 : $res2[$ymd]['reward_diamand_num_3'];

            $list[] = $item;
        }

        return $list;
    }

    //2023双12折扣礼包数据概述
    public function sse2023RateBagStat(Request $request)
    {
        $res1 = DB::connection($this->activityConn)->table($this->sse2023EventTable)
            ->where('type', 'buyRateBag')
            ->selectRaw('ymd,value1,count(DISTINCT user_id) as useNum, count(1) as buyNum')
            ->groupBy('ymd', 'value1')
            ->get();
        $res1 = objToArray($res1);

        $list = [];
        foreach ($res1 as $val) {
            $list[strtotime($val['ymd'])]['ymd'] = $val['ymd'];
            $list[strtotime($val['ymd'])]['useNum_' . $val['value1']] = $val['useNum'];
            $list[strtotime($val['ymd'])]['buyNum_' . $val['value1']] = $val['buyNum'];
        }
        rsort($list);

        foreach ($list as $key => $val) {
            $val['useNum_1'] = empty($val['useNum_1']) ? 0 : $val['useNum_1'];
            $val['buyNum_1'] = empty($val['buyNum_1']) ? 0 : $val['buyNum_1'];
            $val['useNum_2'] = empty($val['useNum_2']) ? 0 : $val['useNum_2'];
            $val['buyNum_2'] = empty($val['buyNum_2']) ? 0 : $val['buyNum_2'];
            $val['useNum_3'] = empty($val['useNum_3']) ? 0 : $val['useNum_3'];
            $val['buyNum_3'] = empty($val['buyNum_3']) ? 0 : $val['buyNum_3'];
            $val['useNum_4'] = empty($val['useNum_4']) ? 0 : $val['useNum_4'];
            $val['buyNum_4'] = empty($val['buyNum_4']) ? 0 : $val['buyNum_4'];
            $val['useNum_5'] = empty($val['useNum_5']) ? 0 : $val['useNum_5'];
            $val['buyNum_5'] = empty($val['buyNum_5']) ? 0 : $val['buyNum_5'];
            $list[$key] = $val;
        }

        return $list;
    }

    //2023双12折扣礼物数据概述
    public function sse2023GiftStat(Request $request)
    {
        //活动开始日期
        $finalsTimeSt = '1702224000';//2023-12-11 00:00:00
        //活动结束日期
        $finalsTimeEt = '1702483199';//2023-12-13 23:59:59

        $res1 = DB::connection($this->connection)->table($this->giftStreamTable)
            ->where('change_timestamp', '>=', $finalsTimeSt)
            ->where('change_timestamp', '<=', $finalsTimeEt)
            ->where('type', 1)
            ->whereIn('gift_id', [184, 185, 186])
            ->selectRaw("gift_id,from_unixtime(change_timestamp, '%Y-%m-%d') as ymd,count(DISTINCT from_user_id) as useNum, count(stream_id) as sendNum,sum(gift_price*gift_num) as diamandNum")
            ->groupBy('ymd', 'gift_id')
            ->get();
        $res1 = objToArray($res1);
        $list = [];
        foreach ($res1 as $val) {
            $list[$val['ymd']][$val['gift_id']] = $val;
        }

        $listEx = [];
        foreach ($list as $key => $val) {
            $item = [];
            //日期
            $item['ymd'] = $key;

            //衰败城市
            $giftId = 184;
            //送出人数
            $item['useNum_184'] = empty($val[184]['useNum']) ? 0 : $val[184]['useNum'];
            //送出次数
            $item['sendNum_184'] = empty($val[184]['sendNum']) ? 0 : $val[184]['sendNum'];
            //总计钻石数
            $item['diamandNum_184'] = empty($val[184]['diamandNum']) ? 0 : $val[184]['diamandNum'];

            //巴黎铁塔
            //送出人数
            $item['useNum_185'] = empty($val[185]['useNum']) ? 0 : $val[185]['useNum'];
            //送出次数
            $item['sendNum_185'] = empty($val[185]['sendNum']) ? 0 : $val[185]['sendNum'];
            //总计钻石数
            $item['diamandNum_185'] = empty($val[185]['diamandNum']) ? 0 : $val[185]['diamandNum'];

            //涅槃女神
            //送出人数
            $item['useNum_186'] = empty($val[186]['useNum']) ? 0 : $val[186]['useNum'];
            //送出次数
            $item['sendNum_186'] = empty($val[186]['sendNum']) ? 0 : $val[186]['sendNum'];
            //总计钻石数
            $item['diamandNum_186'] = empty($val[186]['diamandNum']) ? 0 : $val[186]['diamandNum'];

            $listEx[] = $item;
        }

        return $listEx;
    }

    //获取用户公会
    public function _getUserUnion($userIds = [])
    {
        if (empty($userIds)) {
            return [];
        }
        $list = DB::connection($this->connection)->table($this->unionUserTable . ' as a')
            ->join($this->unionTable . ' as b', 'b.id', '=', 'a.un_id')
            ->wherein('b.authentication_status', [1, 2])
            ->whereIn('a.user_id', $userIds)
            ->select(['a.un_id', 'a.user_id', 'b.name'])
            ->get();
        $list = array_column(objToArray($list), null, 'user_id');

        return $list;
    }

    //获取房间公会
    public function _getRoomUnion($roomIds = [])
    {
        if (empty($roomIds)) {
            return [];
        }
        $list = DB::connection($this->connection)->table($this->unionRoomTable . ' as a')
            ->join($this->unionTable . ' as b', 'b.id', '=', 'a.un_id')
            ->join($this->userTable . ' as c', 'b.un_user_id', '=', 'c.user_id')
            ->wherein('b.authentication_status', [1, 2])
            ->whereIn('a.room_id', $roomIds)
            ->select(['a.un_id', 'a.room_id', 'b.name', 'b.un_user_id', 'c.nickname'])
            ->get();
        $list = array_column(objToArray($list), null, 'room_id');

        return $list;
    }

    //2023双旦节榜上有名主播榜
    public function sdj2023RankUser(Request $request)
    {
        $ymd = $request->get('ymd', 0);
        if (!empty($ymd)) {
            $ymd = date('Ymd', strtotime($ymd));
        }

        $query1 = DB::connection($this->activityConn)->table($this->sdj_bsym_event_table)
            ->selectRaw("to_user_id,gift_id,SUM(score) as priceNum")
            ->groupBy('to_user_id', 'gift_id');
        if (!empty($ymd)) {
            $query1 = $query1->where('ymd', $ymd);
        }
        $res1 = $query1->get();
        $res1 = objToArray($res1);

        $userIds = array_column($res1, 'to_user_id');
        $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id', 'nickname', 'uuid')->get();
        $userList = array_column(objToArray($userList), null, 'user_id');

        //用户公会
        $unionUser = $this->_getUserUnion($userIds);

        //总榜获取奖励积分
        $rewardList = [];
        if (empty($ymd)) {
            $rewardList = DB::connection($this->activityConn)->table($this->sdj_bsym_reward_table)
                ->selectRaw("user_id,SUM(score) as rewardScore")
                ->groupBy('user_id')
                ->get();
            $rewardList = array_column(objToArray($rewardList), null, 'user_id');
        }

        $dataHash = [];
        foreach ($res1 as $val) {
            if (empty($dataHash[$val['to_user_id']])) {
                $dataHash[$val['to_user_id']] = [
                    // 排榜名次
                    'rank' => 0,
                    // 主播ID
                    'userId' => $val['to_user_id'],
                    // 主播昵称
                    'nickname' => empty($userList[$val['to_user_id']]['nickname']) ? '' : $userList[$val['to_user_id']]['nickname'],
                    // 归属公会
                    'unionName' => empty($unionUser[$val['to_user_id']]['name']) ? '' : $unionUser[$val['to_user_id']]['name'],
                    // 归属公会ID
                    'unionId' => empty($unionUser[$val['to_user_id']]['un_id']) ? '' : $unionUser[$val['to_user_id']]['un_id'],
                    // 主播积分
                    'userScore' => empty($rewardList[$val['to_user_id']]['rewardScore']) ? 0 : $rewardList[$val['to_user_id']]['rewardScore'],
                    // 钻石（收礼钻石数）
                    'diamandNum' => 0,
                    // 奖励积分
                    'rewardScore' => empty($rewardList[$val['to_user_id']]['rewardScore']) ? 0 : $rewardList[$val['to_user_id']]['rewardScore'],
                    // 礼物积分
                    'gift_189' => 0,
                    // 礼物积分
                    'gift_190' => 0,
                    // 礼物积分
                    'gift_191' => 0,
                    // 礼物积分
                    'gift_192' => 0,
                    // 礼物积分
                    'gift_193' => 0,
                    // 礼物积分
                    'gift_194' => 0,
                ];
            }
            $dataHash[$val['to_user_id']]['userScore'] = $val['priceNum'] + $dataHash[$val['to_user_id']]['userScore'];
            $dataHash[$val['to_user_id']]['diamandNum'] = $val['priceNum'] + $dataHash[$val['to_user_id']]['diamandNum'];
            $dataHash[$val['to_user_id']]['gift_' . $val['gift_id']] = $val['priceNum'] + $dataHash[$val['to_user_id']]['gift_' . $val['gift_id']];
        }

        $dataHash = array_values($dataHash);
        array_multisort(array_column($dataHash, 'userScore'), SORT_DESC, $dataHash);

        $i = 1;
        foreach ($dataHash as $key => $val) {
            $dataHash[$key]['rank'] = $i;
            $i++;
        }

        $result['ymd'] = $ymd;
        $result['list'] = $dataHash;
        return $result;
    }

    //2023双旦节榜上有名富豪榜
    public function sdj2023RankRich(Request $request)
    {
        $query1 = DB::connection($this->activityConn)->table($this->sdj_bsym_event_table)
            ->selectRaw("from_user_id,gift_id,SUM(score) as priceNum")
            ->groupBy('from_user_id', 'gift_id');
        $res1 = $query1->get();
        $res1 = objToArray($res1);

        $userIds = array_column($res1, 'from_user_id');
        $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id', 'nickname', 'uuid')->get();
        $userList = array_column(objToArray($userList), null, 'user_id');

        //用户公会
        $unionUser = $this->_getUserUnion($userIds);

        $dataHash = [];
        foreach ($res1 as $val) {
            if (empty($dataHash[$val['from_user_id']])) {
                $dataHash[$val['from_user_id']] = [
                    // 排榜名次
                    'rank' => 0,
                    // 用户ID
                    'userId' => $val['from_user_id'],
                    // 用户昵称
                    'nickname' => empty($userList[$val['from_user_id']]['nickname']) ? '' : $userList[$val['from_user_id']]['nickname'],
                    // 归属公会
                    'unionName' => empty($unionUser[$val['from_user_id']]['name']) ? '' : $unionUser[$val['from_user_id']]['name'],
                    // 归属公会ID
                    'unionId' => empty($unionUser[$val['from_user_id']]['un_id']) ? '' : $unionUser[$val['from_user_id']]['un_id'],
                    // 活动积分
                    'userScore' => 0,
                    // 礼物积分
                    'gift_189' => 0,
                    // 礼物积分
                    'gift_190' => 0,
                    // 礼物积分
                    'gift_191' => 0,
                    // 礼物积分
                    'gift_192' => 0,
                    // 礼物积分
                    'gift_193' => 0,
                    // 礼物积分
                    'gift_194' => 0,
                ];
            }
            $dataHash[$val['from_user_id']]['userScore'] = $val['priceNum'] + $dataHash[$val['from_user_id']]['userScore'];
            $dataHash[$val['from_user_id']]['gift_' . $val['gift_id']] = $val['priceNum'] + $dataHash[$val['from_user_id']]['gift_' . $val['gift_id']];
        }

        $dataHash = array_values($dataHash);
        array_multisort(array_column($dataHash, 'userScore'), SORT_DESC, $dataHash);

        $i = 1;
        foreach ($dataHash as $key => $val) {
            $dataHash[$key]['rank'] = $i;
            $i++;
        }

        $result['list'] = $dataHash;
        return $result;
    }

    //2023双旦节榜上有名房间榜
    public function sdj2023RankRoom(Request $request)
    {
        $query1 = DB::connection($this->activityConn)->table($this->sdj_bsym_event_table)
            ->selectRaw("room_id,gift_id,SUM(score) as priceNum")
            ->groupBy('room_id', 'gift_id');
        $res1 = $query1->get();
        $res1 = objToArray($res1);

        $roomIds = array_column($res1, 'room_id');
        $roomList = DB::connection($this->connection)->table($this->roomTable)->whereIn('room_id', $roomIds)->select('room_id', 'name')->get();
        $roomList = array_column(objToArray($roomList), null, 'room_id');

        //房间公会
        $unionRoom = $this->_getRoomUnion($roomIds);

        $dataHash = [];
        foreach ($res1 as $val) {
            if (empty($dataHash[$val['room_id']])) {
                $dataHash[$val['room_id']] = [
                    // 排榜名次
                    'rank' => 0,
                    // 房间ID
                    'roomId' => $val['room_id'],
                    // 房间昵称
                    'roomName' => empty($roomList[$val['room_id']]['name']) ? '' : $roomList[$val['room_id']]['name'],
                    // 归属公会
                    'unionName' => empty($unionRoom[$val['room_id']]['name']) ? '' : $unionRoom[$val['room_id']]['name'],
                    // 归属公会ID
                    'unionId' => empty($unionRoom[$val['room_id']]['un_id']) ? '' : $unionRoom[$val['room_id']]['un_id'],
                    // 公会长ID
                    'unionUserId' => empty($unionRoom[$val['room_id']]['un_user_id']) ? '' : $unionRoom[$val['room_id']]['un_user_id'],
                    // 公会长昵称
                    'unionUserName' => empty($unionRoom[$val['room_id']]['nickname']) ? '' : $unionRoom[$val['room_id']]['nickname'],
                    // 活动积分
                    'userScore' => 0,
                    // 礼物积分
                    'gift_189' => 0,
                    // 礼物积分
                    'gift_190' => 0,
                    // 礼物积分
                    'gift_191' => 0,
                    // 礼物积分
                    'gift_192' => 0,
                    // 礼物积分
                    'gift_193' => 0,
                    // 礼物积分
                    'gift_194' => 0,
                ];
            }
            $dataHash[$val['room_id']]['userScore'] = $val['priceNum'] + $dataHash[$val['room_id']]['userScore'];
            $dataHash[$val['room_id']]['gift_' . $val['gift_id']] = $val['priceNum'] + $dataHash[$val['room_id']]['gift_' . $val['gift_id']];
        }

        $dataHash = array_values($dataHash);
        array_multisort(array_column($dataHash, 'userScore'), SORT_DESC, $dataHash);

        $i = 1;
        foreach ($dataHash as $key => $val) {
            $dataHash[$key]['rank'] = $i;
            $i++;
        }

        $result['list'] = $dataHash;
        return $result;
    }

    //2023双旦节好运连连每日数据
    public function sdj2023HyllDatStat(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $userId = $request->input('user_id', 0);
        $ymd = $request->input('ymd', 0);
        if (!empty($ymd)) {
            $ymd = date('Ymd', strtotime($ymd));
        }

        $query1 = DB::connection($this->activityConn)->table($this->sdj_hyll_day_stat_table)->orderBy('ymd', 'desc');
        if (!empty($userId)) {
            $query1 = $query1->where('user_id', $userId);
        }
        if (!empty($ymd)) {
            $query1 = $query1->where('ymd', $ymd);
        }

        $result = $query1->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        if (!empty($result['data'])) {
            $list = [];
            foreach ($result['data'] as $val) {
                $item = $val;
                // 钻石消耗
                $item['use_diamand_num'] = $val['s_diamand_num'] + $val['j_diamand_num'];
                $list[] = $item;
            }
            $result['data'] = $list;
        }

        return $result;

    }

    //留言板列表
    public function lybList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $userId = $request->input('user_id', 0);
        $toUserId = $request->input('to_user_id', 0);
        $bStatus = $request->input('b_status', -1);

        $query1 = DB::connection($this->connection)->table($this->lybTable);
        if (!empty($userId)) {
            $query1 = $query1->where('user_id', $userId);
        }
        if (!empty($toUserId)) {
            $query1 = $query1->where('to_user_id', $toUserId);
        }
        if ($bStatus >= 0) {
            $query1 = $query1->where('b_status', $bStatus);
        }
        $result = $query1->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);
        if (!empty($result['data'])) {
            $userIds = array_merge(array_column($result['data'], 'user_id'), array_column($result['data'], 'to_user_id'));
            $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id', 'nickname', 'uuid')->get();
            $userList = array_column(objToArray($userList), null, 'user_id');

            $list = [];
            foreach ($result['data'] as $val) {
                $item = $val;
                $item['userName'] = empty($userList[$val['user_id']]['nickname']) ? '' : $userList[$val['user_id']]['nickname'];
                $item['toUserName'] = empty($userList[$val['to_user_id']]['nickname']) ? '' : $userList[$val['to_user_id']]['nickname'];

                $list[] = $item;
            }
            $result['data'] = $list;
        }

        return $result;
    }

    //留言板人工审核
    public function lybAudit($params)
    {
        $res = curl_request(getKkaudioApiUrl() . '/inner/full/lybFullScreen', $params, 'urlencoded', 'POST');
        return curlResultHandle($res);
    }

    //按照每小时统计房间流水
    public function roomDataByHour($roomId, $startTime, $endTime)
    {
        //时间转换
        $startTime = strtotime($startTime);
        $startTime = date('Y-m-d H', $startTime) . ':00:00';
        $endTime = strtotime($endTime);
        $endTime = date('Y-m-d H', $endTime) . ':59:59';
        $res = DB::connection($this->connection)
            ->table($this->unionRoomStreamTable)
            ->selectRaw(DB::raw("room_id,DATE_FORMAT(created_at, '%Y-%m-%d %H' ) as time,
            SUM( data_value*data_num) as total "))
            ->where('room_id', $roomId)
            ->where('created_at', '>=', $startTime)
            ->where('created_at', '<=', $endTime)
            ->groupBy('time')
            ->get();
        return $res;
    }

    //玩法汇总数据表
    public function gameplaySumStatList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $userId = $request->input('user_id', 0);
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');

        //彩蛋
        $query1 = DB::connection($this->connection)->table($this->colorGiftRateAwardStreamTable)
            ->selectRaw('user_id, sum(color_gift_price) as color_gift_price, sum(award_gift_price) as award_gift_price');
        if (!empty($userId)) {
            $query1 = $query1->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data1 = $query1->groupBy('user_id')->get();
        $data1 = array_column(objToArray($data1), null, 'user_id');

        //森林狩猎
        //$grasslandsheepPrice = 10;
        $grasslandsheepPrice = 100;
        //消耗
        $query2 = DB::connection($this->activityConn)->table($this->lc_event_v3_table)
            ->selectRaw('user_id, sum(basePrice) as basePrice')->where('is_delete', 0);
        if (!empty($userId)) {
            $query2 = $query2->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query2 = $query2->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query2 = $query2->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data2 = $query2->groupBy('user_id')->get();
        $data2 = array_column(objToArray($data2), null, 'user_id');
        //产出
        $query3 = DB::connection($this->activityConn)->table($this->lc_event_v3_table)
            ->selectRaw('user_id, sum(rewardPrice) as rewardPrice')->where('is_delete', 0)->where('status', 1);
        if (!empty($userId)) {
            $query3 = $query3->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query3 = $query3->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query3 = $query3->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data3 = $query3->groupBy('user_id')->get();
        $data3 = array_column(objToArray($data3), null, 'user_id');

        //星辰变普通
        $query4 = DB::connection($this->connection)->table($this->wishPoolsDrawTable)
            ->where('pool_level', 1000)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {
            $query4 = $query4->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query4 = $query4->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query4 = $query4->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data4 = $query4->groupBy('user_id')->get();
        $data4 = array_column(objToArray($data4), null, 'user_id');

        //星辰变高级
        $query6 = DB::connection($this->connection)->table('wish_pools_order')
            ->where('pool_level', 1001)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {

            $query6 = $query6->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query6 = $query6->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query6 = $query6->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data6 = $query6->groupBy('user_id')->get();
        $data6 = array_column(objToArray($data6), null, 'user_id');
        //许愿转盘
        $query5 = DB::connection($this->connection)->table($this->wishWheelDrawTable)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {
            $query5 = $query5->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query5 = $query5->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query5 = $query5->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data5 = $query5->groupBy('user_id')->get();
        $data5 = array_column(objToArray($data5), null, 'user_id');


        //五连绝世
        $query12 = DB::connection($this->connection)->table($this->hopePoolsDrawTable)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {
            $query12 = $query12->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query12 = $query12->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query12 = $query12->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data12 = $query12->groupBy('user_id')->get();
        $data12 = array_column(objToArray($data12), null, 'user_id');


        //擂主游戏一阶段
        $query7 = DB::connection($this->connection)->table($this->wishDefenderDrawTable)
            ->where('pool_level', 1000)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {
            $query7 = $query7->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query7 = $query7->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query7 = $query7->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data7 = $query7->groupBy('user_id')->get();
        $data7 = array_column(objToArray($data7), null, 'user_id');
        //擂主游戏二阶段
        $query8 = DB::connection($this->connection)->table($this->wishDefenderDrawTable)
            ->where('pool_level', 1001)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {
            $query8 = $query8->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query8 = $query8->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query8 = $query8->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data8 = $query8->groupBy('user_id')->get();
        $data8 = array_column(objToArray($data8), null, 'user_id');
        //擂主游戏三阶段
        $query9 = DB::connection($this->connection)->table($this->wishDefenderDrawTable)
            ->where('pool_level', 1002)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {
            $query9 = $query9->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query9 = $query9->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query9 = $query9->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data9 = $query9->groupBy('user_id')->get();
        $data9 = array_column(objToArray($data9), null, 'user_id');
        //擂主游戏四阶段
        $query10 = DB::connection($this->connection)->table($this->wishDefenderDrawTable)
            ->where('pool_level', 1003)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if (!empty($userId)) {
            $query10 = $query10->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query10 = $query10->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query10 = $query10->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data10 = $query10->groupBy('user_id')->get();
        $data10 = array_column(objToArray($data10), null, 'user_id');

        //擂主游戏发放礼物
        $query11 = DB::connection($this->connection)->table('wish_defender_winner as a')
            ->leftJoin('gift_library as b', 'a.gift_id', '=', 'b.gift_id')
            ->selectRaw('user_id, sum(0) as prop_diamond_num, sum(1*price) as reward_diamond_num');
        if (!empty($userId)) {
            $query11 = $query11->where('user_id', $userId);
        }
        if (!empty($date_st)) {
            $query11 = $query11->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if (!empty($date_ed)) {
            $query11 = $query11->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data11 = $query11->groupBy('user_id')->get();
        $data11 = array_column(objToArray($data11), null, 'user_id');

        // $data1 = [];
        // $data2 = [];
        // $data3 = [];
        // $data4 = [];
        // $data5 = [];
        // $data6 = [];
        // $data7 = [];
        // $data8 = [];
        // $data9 = [];
        // $data10 = [];
        // $data11 = [];

        //用户信息
        $userIdsAll = array_unique(array_merge(array_column($data1, 'user_id'), array_column($data2, 'user_id'), array_column($data3, 'user_id'), array_column($data4, 'user_id'), array_column($data5, 'user_id'), array_column($data6, 'user_id'), array_column($data7, 'user_id'), array_column($data8, 'user_id'), array_column($data9, 'user_id'), array_column($data10, 'user_id'), array_column($data11, 'user_id'), array_column($data12, 'user_id')));
        rsort($userIdsAll);
        //分页
        $userIds = array_slice($userIdsAll, ($page - 1) * $limit, $limit);
        $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id', 'nickname', 'uuid')->get();
        $userList = array_column(objToArray($userList), null, 'user_id');

        //整理
        $list = [];
        $userList = array_values($userList);
        foreach ($userList as $k => $val) {
            $item = [];
            $item['id'] = $k + 1;
            $item['user_id'] = $val['user_id'];
            $item['uuid'] = $val['uuid'];
            $item['nickname'] = $val['nickname'];
            //彩蛋
            $item['color_pay_num'] = empty($data1[$item['user_id']]['color_gift_price']) ? 0 : $data1[$item['user_id']]['color_gift_price'];
            $item['color_get_num'] = empty($data1[$item['user_id']]['award_gift_price']) ? 0 : $data1[$item['user_id']]['award_gift_price'];
            //森林狩猎
            $item['gl_pay_num'] = empty($data2[$item['user_id']]['basePrice']) ? 0 : $data2[$item['user_id']]['basePrice'] * $grasslandsheepPrice;
            $item['gl_get_num'] = empty($data3[$item['user_id']]['rewardPrice']) ? 0 : $data3[$item['user_id']]['rewardPrice'] * $grasslandsheepPrice;
            //星辰变普通池
            $item['luck_pay_num'] = empty($data4[$item['user_id']]['prop_diamond_num']) ? 0 : $data4[$item['user_id']]['prop_diamond_num'];
            $item['luck_get_num'] = empty($data4[$item['user_id']]['reward_diamond_num']) ? 0 : $data4[$item['user_id']]['reward_diamond_num'];
            //星辰变高级池
            $item['luck_hig_pay_num'] = empty($data6[$item['user_id']]['prop_diamond_num']) ? 0 : $data6[$item['user_id']]['prop_diamond_num'];
            $item['luck_hig_get_num'] = empty($data6[$item['user_id']]['reward_diamond_num']) ? 0 : $data6[$item['user_id']]['reward_diamond_num'];
            //许愿转盘
            $item['wish_pay_num'] = empty($data5[$item['user_id']]['prop_diamond_num']) ? 0 : $data5[$item['user_id']]['prop_diamond_num'];
            $item['wish_get_num'] = empty($data5[$item['user_id']]['reward_diamond_num']) ? 0 : $data5[$item['user_id']]['reward_diamond_num'];
            //擂主游戏一阶
            $item['wish_defender_pay_num1'] = empty($data7[$item['user_id']]['prop_diamond_num']) ? 0 : $data7[$item['user_id']]['prop_diamond_num'];
            $item['wish_defender_get_num1'] = empty($data7[$item['user_id']]['reward_diamond_num']) ? 0 : $data7[$item['user_id']]['reward_diamond_num'];
            //擂主游戏二阶阶
            $item['wish_defender_pay_num2'] = empty($data8[$item['user_id']]['prop_diamond_num']) ? 0 : $data8[$item['user_id']]['prop_diamond_num'];
            $item['wish_defender_get_num2'] = empty($data8[$item['user_id']]['reward_diamond_num']) ? 0 : $data8[$item['user_id']]['reward_diamond_num'];
            //擂主游戏三阶阶
            $item['wish_defender_pay_num3'] = empty($data9[$item['user_id']]['prop_diamond_num']) ? 0 : $data9[$item['user_id']]['prop_diamond_num'];
            $item['wish_defender_get_num3'] = empty($data9[$item['user_id']]['reward_diamond_num']) ? 0 : $data9[$item['user_id']]['reward_diamond_num'];
            //擂主游戏四阶阶
            $item['wish_defender_pay_num4'] = empty($data10[$item['user_id']]['prop_diamond_num']) ? 0 : $data10[$item['user_id']]['prop_diamond_num'];
            $item['wish_defender_get_num4'] = empty($data10[$item['user_id']]['reward_diamond_num']) ? 0 : $data10[$item['user_id']]['reward_diamond_num'];
            //擂主游戏发放礼物
            $item['wish_defender_pay_send_gift'] = empty($data11[$item['user_id']]['prop_diamond_num']) ? 0 : $data11[$item['user_id']]['prop_diamond_num'];
            $item['wish_defender_get_send_gift'] = empty($data11[$item['user_id']]['reward_diamond_num']) ? 0 : $data11[$item['user_id']]['reward_diamond_num'];
            //五连绝世
            $item['hope_pay_num'] = empty($data12[$item['user_id']]['prop_diamond_num']) ? 0 : $data12[$item['user_id']]['prop_diamond_num'];
            $item['hope_get_num'] = empty($data12[$item['user_id']]['reward_diamond_num']) ? 0 : $data12[$item['user_id']]['reward_diamond_num'];

            //合计
//            $item['pay_num'] = $item['color_pay_num'] + $item['gl_pay_num'] + $item['luck_pay_num'] + $item['wish_pay_num'];
//            $item['get_num'] = $item['color_get_num'] + $item['gl_get_num'] + $item['luck_get_num'] + $item['wish_get_num'];
            $item['pay_num'] = $item['hope_pay_num'] + $item['color_pay_num'] + $item['gl_pay_num'] + $item['luck_pay_num'] + $item['wish_pay_num'] + $item['luck_hig_pay_num'] + $item['wish_defender_pay_num1'] + $item['wish_defender_pay_num2'] + $item['wish_defender_pay_num3'] + $item['wish_defender_pay_num4'] + $item['wish_defender_pay_send_gift'];
            $item['get_num'] = $item['hope_get_num'] + $item['color_get_num'] + $item['gl_get_num'] + $item['luck_get_num'] + $item['wish_get_num'] + $item['luck_hig_get_num'] + $item['wish_defender_get_num1'] + $item['wish_defender_get_num2'] + $item['wish_defender_get_num3'] + $item['wish_defender_get_num4'] + $item['wish_defender_get_send_gift'];
            //投入产出比：产出/消耗
            $item['profit_rate'] = empty($item['pay_num']) ? 0 : round(($item['get_num'] * 100) / $item['pay_num'], 4);
            //盈亏：产出-消耗
            $item['profit_num'] = $item['get_num'] - $item['pay_num'];
            //获取用户设备信息
            $request->offsetSet('user_id', $val['user_id']);
            $res = $this->gameplaySumStatListChild($request);
            $item['has_child'] = empty($res) ? 0 : 1;
            $list[] = $item;
        }


        // dd($data12);
        //汇总数据
        $all = [];
        $all['userNum'] = count($userIdsAll);
        $all['pay_num'] = array_sum(array_column($data1, 'color_gift_price')) + array_sum(array_column($data2, 'basePrice')) * $grasslandsheepPrice + array_sum(array_column($data4, 'prop_diamond_num')) + array_sum(array_column($data5, 'prop_diamond_num')) + array_sum(array_column($data6, 'prop_diamond_num'))
            + array_sum(array_column($data7, 'prop_diamond_num')) + array_sum(array_column($data8, 'prop_diamond_num')) + array_sum(array_column($data9, 'prop_diamond_num')) + array_sum(array_column($data10, 'prop_diamond_num')) + array_sum(array_column($data11, 'prop_diamond_num')) + array_sum(array_column($data12, 'prop_diamond_num'));
        $all['get_num'] = array_sum(array_column($data1, 'award_gift_price')) + array_sum(array_column($data3, 'rewardPrice')) * $grasslandsheepPrice + array_sum(array_column($data4, 'reward_diamond_num')) + array_sum(array_column($data5, 'reward_diamond_num')) + array_sum(array_column($data6, 'reward_diamond_num'))
            + array_sum(array_column($data7, 'reward_diamond_num')) + array_sum(array_column($data8, 'reward_diamond_num')) + array_sum(array_column($data9, 'reward_diamond_num')) + array_sum(array_column($data10, 'reward_diamond_num')) + array_sum(array_column($data11, 'reward_diamond_num')) + array_sum(array_column($data12, 'reward_diamond_num'));
        $all['profit_rate'] = empty($all['pay_num']) ? 0 : round(($all['get_num'] * 100) / $all['pay_num'], 4);
        $all['profit_num'] = $all['pay_num'] - $all['get_num'];

        $result = ['list' => $list, 'all' => $all, 'total' => count($userIdsAll)];

        return $result;
    }

    //玩法汇总数据表(同设备下)
    public function gameplaySumStatListChild(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $user_id = $request->input('user_id');
        do {
            $code = 20000;
            $msg = 'ok';
            $data = [];
            if (empty($user_id)) {
                $code = 40000;
                break;
            }
            //获取用户设备信息
            $userInfo = DB::connection($this->connection)->table($this->userTable)->where('user_id', $user_id)->first();
            $userInfo = objToArray($userInfo);
            $deviceStr = $userInfo['guid'];
            //获取同一设备的用户id
            $userArr = DB::connection($this->connection)->table($this->userTable)->where('guid', $deviceStr)->where('user_id', '!=', $user_id)->get();
            $userArr = objToArray($userArr);
            $userIds = array_column($userArr, 'user_id');
            $userInfos = array_column($userArr, null, 'user_id');

            //彩蛋
            $query1 = DB::connection($this->connection)->table($this->colorGiftRateAwardStreamTable)
                ->selectRaw('user_id, sum(color_gift_price) as color_gift_price, sum(award_gift_price) as award_gift_price');
            if (!empty($date_st)) {
                $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
            }
            if (!empty($date_ed)) {
                $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
            }
            $query1 = $query1->whereIn('user_id', $userIds);
            $data1 = $query1->groupBy('user_id')->get();
            $data1 = array_column(objToArray($data1), null, 'user_id');

            //森林狩猎
            //$grasslandsheepPrice = 10;
            $grasslandsheepPrice = 100;
            //消耗
            $query2 = DB::connection($this->activityConn)->table($this->lc_event_v3_table)
                ->selectRaw('user_id, sum(basePrice) as basePrice')->where('is_delete', 0);
            if (!empty($date_st)) {
                $query2 = $query2->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
            }
            if (!empty($date_ed)) {
                $query2 = $query2->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
            }
            $query2 = $query2->whereIn('user_id', $userIds);
            $data2 = $query2->groupBy('user_id')->get();
            $data2 = array_column(objToArray($data2), null, 'user_id');

            //产出
            $query3 = DB::connection($this->activityConn)->table($this->lc_event_v3_table)
                ->selectRaw('user_id, sum(rewardPrice) as rewardPrice')->where('is_delete', 0)->where('status', 1);
            if (!empty($date_st)) {
                $query3 = $query3->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
            }
            if (!empty($date_ed)) {
                $query3 = $query3->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
            }
            $query3 = $query3->whereIn('user_id', $userIds);
            $data3 = $query3->groupBy('user_id')->get();
            $data3 = array_column(objToArray($data3), null, 'user_id');

            //星辰变普通
            $query4 = DB::connection($this->connection)->table($this->wishPoolsDrawTable)
                ->where('pool_level', 1000)
                ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
            if (!empty($date_st)) {
                $query4 = $query4->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
            }
            if (!empty($date_ed)) {
                $query4 = $query4->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
            }
            $query4 = $query4->whereIn('user_id', $userIds);
            $data4 = $query4->groupBy('user_id')->get();
            $data4 = array_column(objToArray($data4), null, 'user_id');

            //星辰变高级
            $query6 = DB::connection($this->connection)->table($this->wishPoolsDrawTable)
                ->where('pool_level', 1001)
                ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
            if (!empty($date_st)) {
                $query6 = $query6->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
            }
            if (!empty($date_ed)) {
                $query6 = $query6->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
            }
            $query6 = $query6->whereIn('user_id', $userIds);
            $data6 = $query6->groupBy('user_id')->get();
            $data6 = array_column(objToArray($data6), null, 'user_id');

            //许愿转盘
            $query5 = DB::connection($this->connection)->table($this->wishWheelDrawTable)
                ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
            if (!empty($date_st)) {
                $query5 = $query5->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
            }
            if (!empty($date_ed)) {
                $query5 = $query5->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
            }
            $query5 = $query5->whereIn('user_id', $userIds);
            $data5 = $query5->groupBy('user_id')->get();
            $data5 = array_column(objToArray($data5), null, 'user_id');
            foreach ($userInfos as $val) {
                $item = [];
                $item['user_id'] = $val['user_id'];
                $item['uuid'] = $val['uuid'];
                $item['nickname'] = $val['nickname'];
                //彩蛋
                $item['color_pay_num'] = empty($data1[$item['user_id']]['color_gift_price']) ? 0 : $data1[$item['user_id']]['color_gift_price'];
                $item['color_get_num'] = empty($data1[$item['user_id']]['award_gift_price']) ? 0 : $data1[$item['user_id']]['award_gift_price'];
                //森林狩猎
                $item['gl_pay_num'] = empty($data2[$item['user_id']]['basePrice']) ? 0 : $data2[$item['user_id']]['basePrice'] * $grasslandsheepPrice;
                $item['gl_get_num'] = empty($data3[$item['user_id']]['rewardPrice']) ? 0 : $data3[$item['user_id']]['rewardPrice'] * $grasslandsheepPrice;
                //星辰变普通池
                $item['luck_pay_num'] = empty($data4[$item['user_id']]['prop_diamond_num']) ? 0 : $data4[$item['user_id']]['prop_diamond_num'];
                $item['luck_get_num'] = empty($data4[$item['user_id']]['reward_diamond_num']) ? 0 : $data4[$item['user_id']]['reward_diamond_num'];
                //星辰变高级池
                $item['luck_hig_pay_num'] = empty($data6[$item['user_id']]['prop_diamond_num']) ? 0 : $data6[$item['user_id']]['prop_diamond_num'];
                $item['luck_hig_get_num'] = empty($data6[$item['user_id']]['reward_diamond_num']) ? 0 : $data6[$item['user_id']]['reward_diamond_num'];
                //许愿转盘
                $item['wish_pay_num'] = empty($data5[$item['user_id']]['prop_diamond_num']) ? 0 : $data5[$item['user_id']]['prop_diamond_num'];
                $item['wish_get_num'] = empty($data5[$item['user_id']]['reward_diamond_num']) ? 0 : $data5[$item['user_id']]['reward_diamond_num'];
                //合计
                $item['pay_num'] = $item['color_pay_num'] + $item['gl_pay_num'] + $item['luck_pay_num'] + $item['wish_pay_num'];
                $item['get_num'] = $item['color_get_num'] + $item['gl_get_num'] + $item['luck_get_num'] + $item['wish_get_num'];
                //投入产出比：产出/消耗
                $item['profit_rate'] = empty($item['pay_num']) ? 0 : round(($item['get_num'] * 100) / $item['pay_num'], 4);
                //盈亏：产出-消耗
                $item['profit_num'] = $item['get_num'] - $item['pay_num'];

                $data[] = $item;
            }
        } while (false);
        return $data;
    }

    //主播私信数量列表
    public function getIdentityMsgStatisticsList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $union_id = $request->input('union_id', 0);
        $user_id = $request->input('user_id', 0);
        $date_st = $request->input('date_st', '');
        $date_ed = $request->input('date_ed', '');

        $query = DB::connection($this->connection)->table($this->userMsgTable);
        if (!empty($union_id)) {
            $query = $query->where('union_id', $union_id);
        } else {
            $query = $query->where('union_id', '>', 0);
        }
        if (!empty($user_id)) {
            $query = $query->where('from_user_id', $user_id);
        }
        if (!empty($date_st)) {
            $date_st = date('Ymd', strtotime($date_st));
            $query = $query->where('ymd', '>=', $date_st);
        }
        if (!empty($date_ed)) {
            $date_ed = date('Ymd', strtotime($date_ed));
            $query = $query->where('ymd', '<=', $date_ed);
        }
        if (!empty($userId)) {
            $query = $query->where('from_user_id', $userId);
        }
        //全部用户的
        $data = $query->selectRaw('from_user_id, union_id, sum(sms_num) as sms_num, count(DISTINCT to_user_id) as user_num, min(created_at) as created_at, max(updated_at) as updated_at')
            ->groupBy('from_user_id', 'union_id')
            ->paginate($limit, ['*'], 'page', $page);
        $data = objToArray($data);

        $list = [];
        if (!empty($data['data'])) {
            $userIds = array_column($data['data'], 'from_user_id');
            $unionIds = array_column($data['data'], 'union_id');
            //对新用户的数据
            $data2 = $query->where('if_new', 1)->whereIn('from_user_id', $userIds)->selectRaw('from_user_id, union_id, sum(sms_num) as sms_num, count(DISTINCT to_user_id) as user_num')
                ->groupBy('from_user_id', 'union_id')
                ->get();
            $data2 = objToArray($data2);
            $data3 = [];
            foreach ($data2 as $val) {
                $data3[$val['union_id'] . ':' . $val['from_user_id']] = $val;
            }

            $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id', 'nickname', 'uuid')->get();
            $userList = array_column(objToArray($userList), null, 'user_id');

            $unionList = DB::connection($this->connection)->table($this->unionTable)->whereIn('id', $unionIds)->select('id', 'name')->get();
            $unionList = array_column(objToArray($unionList), null, 'id');

            foreach ($data['data'] as $val) {
                $val['new_use_num'] = empty($data3[$val['union_id'] . ':' . $val['from_user_id']]['user_num']) ? 0 : $data3[$val['union_id'] . ':' . $val['from_user_id']]['user_num'];
                $val['new_sms_num'] = empty($data3[$val['union_id'] . ':' . $val['from_user_id']]['sms_num']) ? 0 : $data3[$val['union_id'] . ':' . $val['from_user_id']]['sms_num'];

                $val['uuid'] = empty($userList[$val['from_user_id']]['uuid']) ? '' : $userList[$val['from_user_id']]['uuid'];
                $val['nickname'] = empty($userList[$val['from_user_id']]['nickname']) ? '' : $userList[$val['from_user_id']]['nickname'];

                $val['union_name'] = empty($unionList[$val['union_id']]['name']) ? '' : $unionList[$val['union_id']]['name'];

                $list[] = $val;
            }
        }

        $result = ['data' => $list, 'total' => $data['total']];

        return $result;
    }

    //用户资产明细列表
    public function userWalletStatList(Request $request)
    {
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $user_id = $request->input('user_id', 0);

        $query = DB::connection($this->connection)->table($this->financeWalletTable . ' as a')
            ->leftJoin($this->userTable . ' as b', 'a.user_id', '=', 'b.user_id');
        if (!empty($user_id)) {
            $query = $query->where('a.user_id', $user_id);
        }
        $data = $query->paginate($limit, ['a.diamond_balance', 'a.kk_balance', 'a.user_id', 'b.uuid', 'b.nickname'], 'page', $page);
        $data = objToArray($data);

        //汇总数据
        $res2 = DB::connection($this->connection)->table($this->financeWalletTable)
            ->selectRaw('count(DISTINCT user_id) as user_num, sum(diamond_balance) as diamond_balance, sum(kk_balance) as kk_balance')
            ->first();
        $res2 = objToArray($res2);
        $res3 = DB::connection($this->connection)->table($this->giftBagTable . ' as a')
            ->leftJoin($this->giftTable . ' as b', 'a.gift_id', '=', 'b.gift_id')
            ->where('a.avail', '>', 0)
            ->selectRaw('sum(avail*price) as bag_diamond_num')
            ->first();
        $res3 = objToArray($res3);
        $all = [];
        $all['user_num'] = empty($res2['user_num']) ? 0 : $res2['user_num'];
        $all['diamond_balance'] = empty($res2['diamond_balance']) ? 0 : $res2['diamond_balance'];
        $all['kk_balance'] = empty($res2['kk_balance']) ? 0 : $res2['kk_balance'];
        $all['bag_diamond_num'] = empty($res3['bag_diamond_num']) ? 0 : $res3['bag_diamond_num'];

        $list = [];
        if (!empty($data['data'])) {
            $userIds = array_column($data['data'], 'user_id');

            $res4 = DB::connection($this->connection)->table($this->giftBagTable . ' as a')
                ->leftJoin($this->giftTable . ' as b', 'a.gift_id', '=', 'b.gift_id')
                ->where('a.avail', '>', 0)
                ->whereIn('a.user_id', $userIds)
                ->selectRaw('user_id, sum(avail*price) as bag_diamond_num')
                ->groupBy('a.user_id')
                ->get();
            $res4 = objToArray($res4);
            $res4 = array_column($res4, null, 'user_id');

            $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id', 'nickname', 'uuid')->get();
            $userList = array_column(objToArray($userList), null, 'user_id');

            foreach ($data['data'] as $val) {
                $val['uuid'] = empty($userList[$val['user_id']]['uuid']) ? '' : $userList[$val['user_id']]['uuid'];
                $val['nickname'] = empty($userList[$val['user_id']]['nickname']) ? '' : $userList[$val['user_id']]['nickname'];
                $val['bag_diamond_num'] = empty($res4[$val['user_id']]['bag_diamond_num']) ? 0 : $res4[$val['user_id']]['bag_diamond_num'];

                $list[] = $val;
            }
        }

        $result = ['all' => $all, 'data' => $list, 'total' => $data['total']];

        return $result;
    }


}
